For Prof GeGee

profilecyberdreams
5504_assn1_appendices_1.docx
Home>Computer Science homework help>For Prof GeGee

Appendix #1 – Table Creation

Appendix #2 – Binary relationships with different cardinalities.

ACIS 5504 Question 2.jpg

Appendix #3 – Normalization

This example is intended to demonstrate how to complete this requirement:

Un-normalized:

Covert to 1NF:

From 1st NF R1 (Supplier_No, Status, City, Part_No, Quantity)

Functional Dependencies: (Supplier_No, Part_No) Quantity (Supplier_No) Status (Supplier_No) City City Status (Supplier's status is determined by location)

Convert to 2NF

SUPPLIER (Supplier_No, Status, City) SUPPLIER_PART (Supplier_No, Part_No, Quantity)

From 1NF TABLE (GROUP_NUMBER, GROUP_NAME, GROUP_MAXSIZE, PERSON_ID, PERSON_NAME, TITLE, RATE)

Functional Dependencies: (GROUP_NUMBER) GROUP_NAME, GROUP_MAXSIZE (PERSON_ID) PERSON_NAME (PERSON_ID, GROUP_NUMBER) TITLE, RATE

Convert to 2NF

GROUPS (GROUP_NUMBER, GROUP_NAME, GROUP_MAXSIZE) PEOPLE(PERSON_ID, PERSON_NAME)

ASSIGNMENTS(GROUP_NUMBER, PERSON_ID, TITLE, RATE)

From 2NF

GROUPS (GROUP_NUMBER, GROUP_NAME, GROUP_MAXSIZE) PEOPLE(PERSON_ID, PERSON_NAME)

ASSIGNMENTS(GROUP_NUMBER, PERSON_ID, TITLE, RATE)

Functional Dependencies

(TITLE) RATE

Covert to 3NF

GROUPS (GROUP_NUMBER, GROUP_NAME, GROUP_MAXSIZE) PEOPLE(PERSON_ID, PERSON_NAME)

ASSIGNMENTS(GROUP_NUMBER, PERSON_ID, TITLE)

TITLES(TITLE, RATE)

Appendix #4 – E/R Model (created using Crowsfoot notation)

· Airline Database ER Model

Appendix #5 – Rules for Converting E/R model to Relational Model

· Converting ER Models to Relational

· Entities: All entities are converted to relations using the identifier for the entity as the primary key.

· Relationships:

· M-N: Form a new relation with the primary keys of the related entity sets. These keys make up a composite key for the new relation. Add any attributes that may be connected to this relation.

· 1-M: Add the primary key of entity(1) as a foreign key in entity(M).

· (exception: if entity(1) is optional use rule for M-N)

· 1-1: Add the primary key of one entity to the other as a foreign key (exception: If any entity is optional then add the primary key of the mandatory entity to the other. If both entities are optional use rule for M-N)

· is-a: Add the primary key of the super-class entity to the sub-class entity.

· For recursive relationships, simply mimic the rules for the relationships (M:N, 1:N, 1:1) above

Appendix #6 – Converted Relational Model

Converted Relational Model

Pilots(Empid, Hours_flown)

Crew(Empid,Empname,Emp_tele)

Flights(FlightNum,Origin,Destination,Dep_time,Arr_time)

Passengers(Pid,Pname,Ptele,Paddress,Flight_no)

Assigned(Empid,Flight_no,Day-of-week)

· Underline – Primary Key

· Italics (or dashed line) – Foreign Key

· Underline Two or more attributes – Composite Key

Crew Pilots Flights Assigned Passengers EmpID int FK PK EmpName int FK PK EmpTele int FK PK EmpID int FK PK HoursFlown int FK PK FlightNum int FK PK Origin int FK PK Destination int FK PK AssignNum int FK PK EmpID int FK PK FlightNum int FK PK PID int FK PK PFlightNum int FK PK Ptele int FK PK Paddress int FK PK Pname int FK PK IsAssignedTo/ HasCrew M1 M2 M3 M4 IsAssignedBy Assigns M1 M2 M3 M4 AreOn/ Have M1 M2 M3 M4 ISAKindOf M1 M2 M3 M4 DayOfWeek int FK PK

CrewPilotsFlightsAssignedPassengersEmpIDPKEmpNameEmpTeleEmpIDPKHoursFlownFlightNumPKOriginDestinationAssignNumPKEmpIDFlightNumPIDPKPFlightNumPtelePaddressPnameIsAssignedTo/HasCrewIsAssignedByAssignsAreOn/HaveISAKindOfDayOfWeek