cs411-sp07-hw1-sol - CS411 Database Systems Spring 2007...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: CS411 Database Systems Spring 2007 HW #1 Due: 11:30am CST, 2/15/07 Note: Print your name and NetID in the upper right corner of every page of your submission. Handin your homework to Trisha Benson in 4322 SC. Problem 1 ER diagram ( 10 points ) Consider the following information about a company database: • Employees have an Employee ID, a name and a phone number • An employee may have more than one email address • Projects have a project number and a project budget • Departments have a department number, a department name, and a main building • No employee works in more than one department at a time • An employee may work on more than one project at a time, and for each project that they work on, a time percentage is associated Design and draw an ER diagram that captures the information about the company. Be sure to indicate any key and participation constraints (multiplicity constraints). Answer: Figure 1 shows the ER diagram. Grading note: If the students have their own assumptions, and come up with ER diagram that is consistent with their own assumptions, give full credit. S 1 Employee Department Project Email address name EID phone name Dept. No building Proj. No name Has WorkIn WorkOn percentage Figure 1: ER Diagram 1 Problem 2 ER diagram– cont’d ( 10 points ) Modify your solution for Problem 1 above, by introducing managers and interns into the system: • Managers and interns are employees • Each intern has a starting date and an ending date • Each manager has an office and may have one senior manager • Each department is always run by one manager • No manager can lead more than one project at a time Update your ER diagram for Problem 1 to reflect the information given above. Be sure to indicate any key, participation constraints (multiplicity constraints) and roles. Answer: Figure 2 shows the ER diagram. Grading note: If the students have their own assumptions, and come up with ER diagram that is consistent with their own assumptions, give full credit. 2 Employee Department Project Email address name EID phone name Dept. No building Proj. No name Has WorkIn WorkOn percentage IsA IsA Intern StartDate EndDate Manager office Run Lead supervise junior senior Figure 2: ER Diagram 2 Problem 3 Schema Design a) Convert the E/R diagram of Problem 1 to a relational database schema. Don’t forget to indicate the keys for each relation. ( 5 points ) Answer: Employee( EID , Name, Phone, DeptNo) Project( ProjNo , name) Department( DeptNo , name, building) Email( Address ) Has( EID , Address ) WorkOn( EID , ProjNo ) Grading note: Make sure that the WorkIn relationship is absorbed into the Employee relation. This reduces redundancy. b) Convert the manager, intern, employee subclass relationship in Problem 2 into relational schema, using the following three approaches: ER approach, Object-Oriented approach, Null approach....
View Full Document

{[ snackBarMessage ]}

Page1 / 9

cs411-sp07-hw1-sol - CS411 Database Systems Spring 2007...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online