Entity-relationship Diagram Exercises And Answers - Pdf
\tableofcontents \newpage
\sectionExercise 1: University Database \textbfScenario: \\ A university needs a database to manage its courses and students. \beginitemize \item Each \textbfStudent has a unique student ID, name, and major. \item Each \textbfCourse has a unique course code, title, and credits. \item A student can enroll in many courses. A course can have many students. \item The enrollment date should be recorded as an attribute of the relationship. \enditemize
\sectionExercise 5: Weak Entity Example (Order-Item) \textbfScenario: \\ An e-commerce system has orders and line items. \beginitemize \item \textbfOrder: order\_number (PK), order\_date, customer\_name. \item \textbfLine Item: item\_number (only unique per order), product\_name, quantity, price. \item A line item cannot exist without an order. The combination (order\_number, item\_number) uniquely identifies a line item. \enditemize
\newpage
\documentclass[12pt,a4paper]article \usepackage[utf8]inputenc \usepackagegeometry \geometrymargin=1in \usepackagegraphicx \usepackagetikz \usetikzlibraryshapes,arrows,positioning,fit \usepackagefloat \usepackagehyperref \usepackageenumitem \usepackagetcolorbox
\newpage
\textbfTask: Draw the ERD including entities, attributes, primary keys, and the many-to-many relationship with its attribute. entity-relationship diagram exercises and answers pdf
\sectionExercise 4: Hospital Management (Ternary Relationship) \textbfScenario: \\ A hospital records which doctor treats which patient on which date. \beginitemize \item \textbfDoctor: doctor\_id (PK), name, specialty. \item \textbfPatient: patient\_id (PK), name, illness. \item \textbfTreatment Date: date. \item A doctor may treat many patients on a given date; a patient may be treated by many doctors; and each treatment happens on a specific date. The combination (doctor, patient, date) is unique. \enditemize
\textbfNotation: We use \textbfCrow's Foot notation. \beginitemize \item Entities: Rectangles \item Primary Key: Underlined attribute \item Relationships: Diamonds (or labeled lines) with crow's foot for many side. \item Cardinalities: 1 (one), M (many), (0,1) optional one, etc. \enditemize
\textbfTask: Draw the ERD showing: \beginitemize \item One-to-Many relationship between Department and Employee (works-in). \item One-to-One relationship between Employee and Department (manages). \item Indicate mandatory/optional participation. \enditemize \item A student can enroll in many courses
\vspace2cm \noindent\rule\textwidth0.5pt \textbfAnswer: \beginfigure[H] \centering \begintikzpicture \node[rectangle, draw] (order) ORDER \\ \tiny order\_number (PK) \\ order\_date \\ customer\_name; \node[rectangle, draw, double, below=1.5cm of order] (item) LINE ITEM \\ \tiny (weak entity); \node[diamond, draw, aspect=2, left=0.5cm of $(order.south)!0.5!(item.north)$] (contains) CONTAINS; \draw (order) -- (contains); \draw (contains) -- (item); \node[below=0.2cm of item, align=center] \tiny partial key: item\_number \\ attributes: product\_name, quantity, price; \endtikzpicture \captionWeak Entity LINE ITEM identified by ORDER \endfigure
% Better to use explicit relationship diamond for clarity \endtikzpicture \endfigure \noindent Alternative clear diagram: \beginfigure[H] \centering \begintikzpicture \node[rectangle, draw] (dept) DEPARTMENT; \node[rectangle, draw, below=2cm of dept] (emp) EMPLOYEE; \node[diamond, draw, left=1cm of dept] (works) WORKS\_IN; \node[diamond, draw, right=1cm of dept] (manages) MANAGES;
% Relationship \node[diamond, draw, aspect=2, minimum width=2cm, minimum height=1cm, below=1.2cm of $(student.south)!0.5!(course.south)$] (enroll) ENROLLS; \node[below=0.2cm of enroll, align=center] \tiny enrollment\_date; \draw (dept) -- (manages) node[midway
% Optional: show as associative entity? For clarity, many-to-many with attribute. \endtikzpicture \captionERD for University Enrollment (Many-to-Many with attribute) \endfigure \noindent\textitNote: In physical design, ENROLLS becomes a bridge table containing student\_ID, course\_code, and enrollment\_date.
\draw (dept) -- (manages) node[midway, above] 1; \draw (emp) -- (manages) node[midway, below] 1; \endtikzpicture \captionCompany ERD \endfigure