7BDIN007W – DATA REPOSITORIES PRINCIPLES AND TOOLS ASSESSMENT

PART A

A Sub Part

Documentation in tabular format

Entity name: Outlet

Entity name: Vehicle

Entity: Personal_client

Entity: Corporate_Client

Entity: Booking

Entity: Rent_Agreement

Entity:Staff

Entity: MYEZCAR _Membership

Relationships

• There is one to many relationship between outlet and vehicle.
• There is one to one relationship between booking and client.
• There is one to one relationship between rental agreement and client.
• There is one to one relationship between client and Myezcar benefits.
• There is one to one relationship between staff and outlet.

Conceptual ERD

Figure 1 Shows Conceptual ERD

B Sub Part

• Outlet(Outlet _Code, Outlet_Address, Phone_Number, Fax_Number, Outlet_Manager)
• Vehicle(Vehicle_Registration,V_Model,V_Make, Reg_Date, Engine_Size, Transmission_Type, Rental_Class, Rental_Category, Number_of_Doors, Number_of_Seats, Recent_MOT_Date, Mileage, Allocated_Outlet_code)
• Personal_Client(Client_ID, First_Name, Last_Name, Home_Address, Phone_Number, Email_Address, DOB, Driving_License)
• Corporate_Client(Client_ID, Business_Name, Business_Address, Phone_Number, Fax_Number, Email_Address, Type_of_Business)
• Booking(Booking_code, Pick-up_Location, Drop-off_Location, Rental_Start_Date, Rental_Start_time, Rental_Finish_Date, Rental_Finish_time, Transmission_Type, Rental_Class, Rental_Category, Number_of_Doors, Number_of_Seats, Daily_Rate, Insurance_Cover, Booking_Status, Client_id, Drivers, Additional_Services, Discount_Codes, Payment)
• Rental Agreement(Rental_Agreement_Code, Client_id, Drivers_details,Vehicle_registration, Staff_number, Rental_Start_Date, Rental_Return_Date, Daily_Hire_Rate, Mileage_Included, Insurance_Cover, Fuel_Level, Mileage_at_Start, Faults/Dents)
• Staff (Staff_Number, First_Name, Last_Name, Address, Phone_Number, Mobile_Phone,Date_of_Birth, National_insurancce_number, Date_Joined, Job_title, Salary, Type_of_Contract)
• MYEZCAR_Membership(Client_ID,MYEZCAR_Benefits)

All the entities are determined and their attributes are noted. The primary keys of each of the entities is also identified. The foreign keys are determined for each of the relation to create the logical ERD

PART B

A Sub Part

The steps are

• Finding the closure set of {B, C}. Closure = {B, C}
• The functional dependencies are iterated and new attributes are added to closure set.

B ? DG

Closure = {B, C, D, G}

CD ? A

Closure = {A, B, C, D, G}

A ? E

Closure = {A, B, C, D, E, G}

AC ? B

Closure = {A, B, C, D, E, G}

ABE ? C

Closure = {A, B, C, D, E, G}

AG ? BC

Closure = {A, B, C, D, E, G}

AD ? EG

Closure = {A, B, C, D, E, G}

Therefore, {????, ????}_F⁺= {A, B, C, D, E, G}

B SubPart

1. {C, E} is not candidate key

Justification

F = {CE?A, AB?D, BD?C}, and we need to find {C,E}⁺_F

For functional dependency, CE->A

{C,E}⁺_F = {A, C,E}

The closure does not contains all the attributes of R therefore, {C, E} is not the candidate key.

2. {B, C, E} is a candidate key

Justification:

We need to calculate {B,C,E}⁺_F

For functional dependency, CE->A

{B,C,E}⁺_F = {C, E, A}

For functional dependency, AB?D

{B,C,E}⁺_F = {A, B, C, E, D}

For functional dependency,  BD?C

{B,C,E}⁺_F = {A, B, C, D, E}

The closure contains all the attributes of R therefore, {B,C, E} is the key of R.

The subset of {B,C, E} must not be key in order to {B,C, E} become the candidate key. No singleton subset of {B,C, E} is the key and there are no functional dependencies with the determinants that contain only one attribute therefore, {B,C, E} is also candidate key.

C Sub Part

Logical ERD

The steps are

1. Identify all the entities in the ERD. The entities include

• Candidate
• Assess_Centre
• Exam
• Certificate
• Awarding_body
• Enrolment
• Pre-Requisite
• Assessment

2. Identification of attributes of each of the entities

• Candidate(C_id, c_name, c_addr, c_dob,c_gender, c_email)
• Assess_Centre(ac_code,ac_addr)
• Exam( ex_code,ex_title)
• Certificate(cert_id,cert_title, cert_type, bd_id)
• Awarding_body(bd_id,bd_name,bd_addr,bd_tel_no,bd_url)
• Enrolment(c_id,cert_id,enrol_no, enrol_dt,completion_dt)
• Pre-Requisite(ex_code,pre_ex_code)
• Assessment(c_id,ac_code,ex_code,ass_dt,ass_tm,score,outcome)

3. Identification of the primay keys and making them bold and underline

• Candidate(C_id,c_name, c_addr, c_dob,c_gender, c_email)
• Assess_Centre(ac_code,ac_addr)
• Exam( ex_code,ex_title)
• Certificate(cert_id,cert_title, cert_type, bd_id)
• Awarding_body(bd_id,bd_name,bd_addr,bd_tel_no,bd_url)
• Enrolment(c_id,cert_id,enrol_no, enrol_dt,completion_dt)
• Pre-Requisite(ex_code,pre_ex_code)
• Assessment(c_id,ac_code,ex_code,ass_dt,ass_tm,score,outcome) 
• The primary key of the awarding_body relation has been made the foreign key of the certificate relation.

1. Resolution of M:N relationships

The enrolls for relationship has been resolved by the introduction of the enrolment table which has a relationship with the certificate and candidate.

The primary key of the certificate and candidate relation has been made the foreign key of the enrolment relation.

2. Recursive relationship

The has pre requisite recursive relationship has been resolved by the introduction of the pre requisite table. and two relationships has and is pre requisite for.

The primary key of the exam relation has been made the foreign key of the pre requisite relation and assessment relation.

3. Ternary relationship

Registersrelationship has been resolved by introduction of the assessment table. This relation is linked to three entities including exam, assess_centre and candidate.

• The primary key of the assess_centre relation has been made the foreign key of the assessment relation
• The primary key of the candidate relation has been made the foreign key of the assessment relation.

D Sub Part

The normalized relations are created in following steps.

1. First normal form

The primary keys are identified based on the given functional dependencies and it comes out to be combination of attributes including job_no, s_no, and task_no.

STAFF_ACC  primary keys are {job_no, s_no, task_no}.

1. Second normal form

The attributes that are dependent on any part of the primary key are removed.

Using fd3 (job_no, task_no ? task_dt, task_desc, hours)

Relation:

Tasks (job_no, task_no, task_dt, task_descr, hours)

Using fd2 (job_no, s_no ? s_nm, salary, country, job_dt, job_desc, job_type)

Relation:

STAFF_JOB (job_no, s_no,job_dt, job_desc, job_type)

Using fd1 (s_no ? s_nm, salary, country)

Relation:

STAFF (s_no,s_nm, salary, country)

1. Third normal form

There are no transitive dependencies in the non primary attributes of the relation therefore, relations follow the third normal form.

1. BCNF

The relations are in BCNF as all the functional dependencies have determinants as the part of the primary  key.

The normalized relations are

• TASKS (job_no, task_no, task_dt, task_descr, hours)
• STAFF (s_no, s_nm, salary, country)
• STAFF_JOB (job_no, s_no,job_dt, job_desc, job_type)
• STAFF_ACC (job_no, s_no, task_no)

The foreign keys are

• STAFF_ACC (job_no, s_no) References STAFF_JOB (job_no, s_no)
• STAFF_ACC (job_no, task_no) References TASKS(job_no, task_no)
• STAFF_JOB (s_no) References STAFF(s_no)

Assumptions

• The complete functional dependencies are given.
• There are no additional dependencies apart from given dependencies.