CIS 365 Business Database Concepts Assignment 2 - Normalization / Logical Design

Question

CIS 365 Business Database Concepts Assignment 2 -

Normalization / Logical Design (54 points)

The following assignment is to be drawn using any software drawing package you have access to (PowerPoint, Gliffy, etc.). Identify and label all functional dependencies and primary keys in each FD diagram. Make sure all work is clear and easy to read. Professional quality is expected. Use the methods shown in class for drawing both the dependency diagrams and the logical database schema.

1. (18) Normalize the following relation to 3rd normal form. Identify & draw the complete FDs for all normal forms through to 3NF.

Application Summary: clients rent properties from their owners for a determined amount of time. This is the entity taken from the ERD, which shows the client who rented the property (along with other info about the rental that must be recorded). Primary keys for the entity are identified on the diagram below. (Do not add or delete any attributes).

PAddress – property’s address OName – owner’s name OPhone – owner’s phone

b) Improve the relations to 3NF. Show and label all FDs and all dependency arrows. Identify all primary keys for each FD.

c) Draw the relational schema for the final database table structure. Name each relation and identify all primary and foreign keys.

2. (15) Normalize the following relation to 3rd normal form. Identify & draw the complete FDs for all normal forms through to 3NF.

Application Summary: this data shows scheduled surgeries for patients. The surgery requires one surgeon who has a nurse to assist. Nurses will assist on many types of surgeries. Surgeons and nurses may perform more than one surgery per day, but a patient can have only one surgery per day. The table below showssample data. (Do not add or delete any attributes)

a) Identify all full (above the diagram), partial, and transitive dependencies (below the diagram) and label them.

b) Improve the relations to 3NF. Show and label all FDs and all dependency arrows. Identify all primary keys for each FD.

c) Draw the relational schema for the final database table structure. Name each relation and identify all primary and foreign keys.

3. (21) Normalize the following relation to 3rd normal form. Identify & draw the complete FDs for all normal forms through to 3NF.

Application Summary: customers rent classic one of a kind movies from an online video rental service. CustCC – customer credit card

Studio – where the movie was made

Director – of the movie

a) Identify all full (above the diagram), partial, and transitive dependencies (below the diagram) and label them.

b) Improve the relations to 3NF. Show and label all FDs and all dependency arrows. Identify all primary keys for each FD.

c)   Draw the relational schema for the final database table structure. Name each relation and identify all primary and foreign keys.

Client# | Property# | Owner# | CName | PAddress | RentStarDatel RentEndDate | RentAmt! OName | OPhone

Explanation / Answer

Question 1:

---------------

To understand the whole solution, first we need to look into Normalized form

1NF: A database is in first normal form if it satisfies the following conditions:

Contains only atomic values

There are no repeating groups

  

So here A owner can have multiple properties so we cannot have property ID and Owner ID in same table because doing this we have duplicate data in single table.

so here we split property and owner info in two tables.

  

Table 1 : Column { Property#, PAddress }

Table 2 : Column { Owner#,Property#,Oname,OPhone }

Here we cannot have client details in both the above table beacuse doing this we break the INF form and we will have duplicate and non atomic values in our table. beacuse with client infomration we always

have proeprty details as weel as owner details. otherwise we canot defined the relatioshipl between them.

So As of Now, table 1 and table 2 is in 1NF.

2NF: A database is in second normal form if it satisfies the following conditions:

1. It is in first normal form

2. All non-key attributes are fully functional dependent on the primary key

using 2NF rule, we will maintian the client information in seperate table where client# is primary key and all the other information will be dependent on same.

Table 3: { client#,cName,RentStartSDate,RentEndSDate,RentAmount}

Here dependent information is directly related to client because client can purchase the properties also and properties can or cannot be on rent

so as client comes into the picture these information also come into the existance.

Table 3 : Columns { Client#,CName,RentStartSDate,EndDate, RentAmount }

Now the problem with table 1,2 and 3 is we donot have relationship between client and property so as per the requirement or according to 1NF and 2 NF we should have another table

to maintain that relatioship . you can call it table 4 where column would be like { client# , property#, owner# }.

3NF: A database is in third normal form if it satisfies the following conditions:

1. It is in second normal form

2. There is no transitive functional dependency.

so here by folloing 3NF we should not have transitive functional dependency which we have in table 1,2,3 and 4 because we have owner,property and client information in three table

and where client is functionally dependent on properties and property is functionally dependent on owner and owner is transitively dependent on client via properties.

so we have to normazlized it and according to 3NF we should have table 4 and the inrmation of table 4 should be in table 3 with client info in table 3 so

Table 3: {client#,cName,property#,owner#,RentStartSDate,RentEndSDate,RentAmount}

after applying 3NF, we would have follwing tables

Table 1 : Property -> Column { Property#, PAddress }

Table 2 : Owner -> Column { Owner#,Property##,Oname,OPhone }

Table 3 : Client -> Columns { client#,cName,property##,owner##,RentStartSDate,RentEndSDate,RentAmount}

here all the column ends with '#' character would be primary key for that table and "##' character is used as foriegn key for that table. }

Question 2:

--------------

Table 1: Surgeon -> Column { SurgeonID#, SurgeonName }

Table 2: Nurse -> Column { NurseID#, NurseName }

Table 3: Patient -> Column { PatientID#,PatientName,SurgeonID##,NurseID##,SurgeryDate,SurgeryType }

here all the column ends with '#' character would be primary key for that table and "##' character is used as foriegn key for that table. }

Above given tables follow all the Normalized forms rules.

1. No repeating groups info

2. All the non-key attribute for primary key

3. No transitively dependent should exist between any relatioship.

Question 3:

-----------------

Table 1 -> Movie -> { MovieID, MovieTitle }

Table 2 -> Studio -> { StudioID, Studioname,StudioLocation }

Table 3 -> Direction -> { DirectorID#, DirectorName }

table 4 -> Customer -> { CustID#, CustomerName }

Table 5 -> Transaction -> { CustId#, MovieID##,StudiID#,DirectorId##,CustCC,RentDate,ReturnDate}

The key points masured here to break all the infoiramtion in tables are

1. There may be a case where multiple mobies can be sirected by same director and at same location or studio so here studio and director table is maintianed.

2. Same customer can have multiple movies on rent.

3. Customer can make payment either through Creditcard or cash so here we donot bind the custcc inofrmation in customer table.

if the payment can only be done through card and customer can only have one card then we can put the custcc column from tanasaction table to customer table

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---