system Database help..

question 1

You have just been hired as a consultant for a big airplane manufacturer. Impressed by your background in databases, they want you to completely redesign their database system. Talking with the people in the company, you get the following information:

- The database contains information about employees, factories and parts.

- Each employee has a social security number (SSN), name and salary. An employee is uniquely identified by his or her SSN.

- Each factory has an id, name and a budget. The id uniquely identifies a factory.

- Each part has an id and a name. The id uniquely identifies a part.

- Each employee works in only one factory, a factory can employ many employees.

- Each part is manufactured in at least one factory. A factory can produce many different parts.

1.1) Draw the Entity-Relationship diagram for the above application. Again, if you need more space use the bottom of this Word Doc, or create another Word Doc and put it in there. Just remember to upload all the Word Docs to D2L.

1.2) Show the relational schema that corresponds to the above ER diagram. Make sure to clearly indicate primary keys and foreign keys. These would be the DDL needed to create the tables to support your ERD from 4.1.

1.3)Identify all relationships in the ERD that you created in part 4.1. For each relationship, write a business rule to describe the relationship. E.g. Student has one advisor, but an advisor may have multiple students.

Question 2

Consider the following database instance.

Table name: Student

Primary key: SID

SID	SNAME
1	Joe
2	Susan
3	Ed

Table name: Course

Primary key: CID

CID	CNAME
1	Accounting
2	CIS

Table name: Register

Primary key: SID,CID

Foreign key: SID references Student(SID) on delete cascade

Foreign key: CID references Course(CID) on update cascade

SID	CID
1	2
2	2

Questions on next page

For each of the following questions, show the contents of the database (i,e, re-draw the tables showing the data) after performing the given SQL statements. Assume each SQL statement is performed on the original database instance without considering the effect of the previous statements.

2.1)

delete from Student where sid = 1;

2.2)

update Course set cid = 3 where cid = 2

3.3)

update Register set sid = 3 where sid = 4;