\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
\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
\sectionExercise 3: Company Employees and Departments \textbfScenario: \\ A company consists of departments. Each department has a unique department number, name, and budget. An employee has an employee ID, name, salary, and works in exactly one department. A department may have many employees. Additionally, one employee manages a department (the manager is also an employee). entity-relationship diagram exercises and answers pdf
\sectionExercise 2: Library System \textbfScenario: \\ A library wants to track books and members. \beginitemize \item \textbfBook: ISBN (unique), title, author, year. \item \textbfMember: member\_id (unique), name, phone. \item A member can borrow many books, but a specific copy of a book can be borrowed by only one member at a time (simplified: assume one copy per ISBN). \item A book may be borrowed by many members over time. \item The relationship \textbfBorrows has attributes: borrow\_date, return\_date. \enditemize
% 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;
\tableofcontents \newpage
% Course Entity \node[rectangle, draw, minimum width=2.5cm, minimum height=1cm, right=of student] (course) COURSE; \node[above=0.2cm of course] \textbfCourse; \node[below=0.1cm of course, align=center] \tiny course\_code (PK)\\ title\\ credits;
\newpage
\newpage
% 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.
\vspace2cm \noindent\rule\textwidth0.5pt \textbfAnswer: \beginfigure[H] \centering \begintikzpicture[node distance=2cm] \node[rectangle, draw] (doctor) DOCTOR; \node[rectangle, draw, below left=2cm and -0.5cm of doctor] (patient) PATIENT; \node[rectangle, draw, below right=2cm and -0.5cm of doctor] (date) DATE; \node[diamond, draw, aspect=2, below=1.5cm of doctor] (treats) TREATS; \draw (doctor) -- (treats); \draw (patient) -- (treats); \draw (date) -- (treats); \node at ($(doctor)!0.5!(treats)$) [left] M; \node at ($(patient)!0.5!(treats)$) [left] M; \node at ($(date)!0.5!(treats)$) [right] M; \endtikzpicture \captionTernary Relationship: TREATS \endfigure \noindent The relationship has cardinality M:M:M. In tables, the composite key is (doctor\_id, patient\_id, date).
\newpage
\vspace2cm \noindent\rule\textwidth0.5pt \textbfAnswer: \beginfigure[H] \centering \begintikzpicture \node[rectangle, draw] (vehicle) VEHICLE \\ \tiny VIN (PK) \\ make \\ model \\ year; \node[rectangle, draw, below left=1.5cm and 1cm of vehicle] (car) CAR \\ \tiny doors \\ fuel\_type; \node[rectangle, draw, below right=1.5cm and 1cm of vehicle] (truck) TRUCK \\ \tiny cargo\_capacity \\ axles; \draw (vehicle) -- (car) node[midway, left] ISA (d); \draw (vehicle) -- (truck) node[midway, right] ISA (d); \endtikzpicture \captionGeneralization: Vehicle supertype with Car and Truck subtypes \endfigure