Eer diagram assignment PDF

Preview

Summary

Download Eer diagram assignment PDF

Description

Week 4 – Group Assignment 1). Mapping EER Diagram to a relational schema using the techniques described in this chapter: Designed the following relational schema by referring the previous week EER diagram using draw.io tool

Above diagram is the relational schema for the FAME organization, which is designed using the techniques explained in this chapter. 

Customer ID is the primary key in the CUSTOMER table, used to retrieve the unique row in the customer table.



Manager ID is the primary key in the MANAGER table, using this Manager ID we can able to retrieve all other details of manager from the manager table.



Organizer ID is the primary key and it contains unique ID of the Organizer, which will be helpful to access and retrieve the records of the organizer from the EVENT ORGANIZERS table.



In the BOOKING table, Customer ID and Booking ID are the primary keys which are used to retrieve the Customer Booking details, following are the foreign keys in BOOKING table 1) Organizer ID refers to the Organizer ID in EVENT ORGANIZERS table 2) Customer ID refers to the Customer ID in CUSTOMER table. 3) Artist ID refers to the Artist ID in the ARTIST table. 4) Manager ID refers to the Manager ID in MANAGER table.



Artist ID is the primary key in the ARTIST Table. Artist table have the following foreign key to build a relationship between the ARTIST and MANAGER to define who is the manager of specific artist. 1) Manager ID refers to the Manager ID in MANAGER table.



Date and Artist ID Combination is the primary key in CALENDAR table. Using the data and Artist ID we can able to retrieve the event details of the artist on the specific date. Following is the foreign key 1) Artist ID refers to the Artist ID in ARTIST Table.



Artist ID is the primary key in the ARTIST ACCOUNT table which is used to retrieve the artist account details using the unique artist ID. Following is the foreign key 1) Artist ID refers to the Artist ID in ARTIST Table.



Payment ID and Customer ID are the primary key in the PAYMENT table. Following are the foreign key 1) Customer ID refers to the Customer ID in the CUSTOMER Table. 2) Artist ID refers to the Artist ID in the ARTIST Table.



The strong entities are mapped to relation and their attributes are also shown



The underline attributes are the primary key for the give relation and the dotted underline is the foreign key referenced from the relation mapped by an arrow.



The compound entities are also mapped from the mapped relations

2.Analyze and document the functional dependencies in each relation identified: As per the requirement, the relational schema of the FAME organization system must be in third normal form to avoid the complication in saving and retrieving the data. A relation is in third normal form (3NF), if it is in second normal form and no transitive dependencies exist. A transitive dependency in a relation is a functional dependency between the primary key and one or more non-key attributes that are dependent on the primary key via another non-key attribute. The advantages of removing transitive dependencies are mainly twofold. First, the amount of data duplication is reduced and therefore your database becomes smaller. The second advantage is data integrity. When duplicated data changes, there's a big risk of updating only some of the data, especially if it's spread out in a number of different places in the database A relation is in second normal form (2NF) if it is in first normal form and contains no partial functional dependencies. A partial functional dependency exists when a non-key attribute is functionally dependent on part (but not all) of the primary key. In the above data model entities like CUSTOMER, MANAGER, EVENT ORGANIZERS, PAYMENT, ARTIST, ARTIST ACCOUNT and BOOKING have no partial dependencies and no multi-valued attributes. We have removed the transitive dependency from the above data model to make it in Third Normal Form (3NF). Initially I have developed ER model which have the following relationship to fetch the delivery details. For example: 1) To retrieve the payment details previously we need to track I following scenario CUSTOMER ID  MANAGER ID  PAYMENT ID To avoid this transitive dependency, we have redesigned the relationship to make the system follow 3NF

CUSTOMER ID  PAYMENT 2) Previously Artist need to check their event bookings like ARTIST ID MANAGER ID BOOKING TO avoid the transitive dependency, created the new attribute Artist ID in BOOKING table ARIST ID BOOKING CUCTOMER and EVENT ORGANIZERS are maintaining the details of Customers, so Event Organizers table is removed and Customer ID is added into the Payment table to maintain the relationship between Payment and Customer.

So the functional dependency can be removed by making Artist ID as a primary key in artist account and then referenced it to foreign key in Payment relation. It’s better to store the details in the Artist Account and then referencing the duplicate entities in the payment relation. Now there is no multi-valued attributes in the relational schema and even partial dependencies and transitive dependencies are removed from the relation schema to make the system supports 3NF. Diagram for the previous question is in 3NF. No changes are required. 3) Does it make sense for FAME to use enterprise keys: FAME should use the enterprise keys which are described as follows. The ER diagram includes the super type/subtype relation. The super type is Object which can be Customer, Event Organizers, Manager or an Artist. So the relation is Object is created with attribute Object ID and Object Type. Thus the relations customer, manager and artist are modified as follows including the Object ID as a super key.

Now, the demographic details of the customer manager and artist store the same details as the name and address details and contact details. So the enterprise keys can be used here by adding the relation PERSON with attributes Object ID, Name, Address, City, State, PhNo. Person ID attribute will be added in the Customer Entity Person ID attribute will be added in the Manager Entity Person ID attribute will be added in the Artist Entity This can be given as follows.

Finally, the revised relation schema will have the following entities with the addition of new entities like Object and Person.

4) If necessary, revisit and modify the EER diagram you have created

In the above revised diagram, the demographic details of the customer, manager and artist store the same details as the name and address details and contact details. So, the enterprise keys can be used here by adding the relation PERSON with attributes Object ID, Name, Address, City, State, PhNo....