COMP2400 - Relational Databases - Database Theory - ACTRides - IT Assignment Help

COMP2400: Relational Databases Theory  IT Assignment Help

Assignment Task:

Question 1
ACTRides is a car rental company which has been founded recently in Canberra. It currently has 5 branches opened in Acton, Belconnen, Dickson, Braddon and Woden, respectively. ACTRides is planning to expand to areas such as Tuggeranong and Manuka in the future. A branch has a unique address but may have
multiple telephone numbers. Each rental car must be associated with exactly one ACTRides branch. A rental car can be identified by its registration number and has the information about the model and the production year. Each employee working at ACTRides has a tax file number (TFN), a name and an address. ACTRides employees consist of managers, mechanics and customer service representatives. Every employee must work at exactly one branch. A customer of ACTRides should provide their drivers license number, their name and a contact number. A customer can rent a car from an ACTRides branch and details about the rental such as the rental period and price should be recorded. If a customer is not satisfied with the rental service provided, this customer may submit a complaint to a customer service representative and thus a distinct reference number and detailed description of the complaint should be recorded. The manager of a branch will handle all the complaints with respect to this branch and has an urgent contact number in case of emergency. Mechanics working at a branch inspect the cars associated with the branch and record the condition and inspection date. A car must be inspected at least two times per year. Each mechanic has a skill level and might have another mechanic as the supervisor. The supervisor must have a higher skill level compared to the mechanics supervised by this supervisor.

Your task is to design an Enhanced Entity Relationship (EER) diagram for the above database, whichshould include entities, relationships, attributes and constraints  wherever appropriate (you can make more assumptions if necessary). You also need to identify the requirements that cannot be captured in an EER-diagram.

Question 2 
Consider the relation schema R={A, B, C, D, E} and the following set Σ of FDs:
• A → B
• AB → DE
• C → B
• BE → AC

2.1 What are the candidate keys of R? Justify your answer (i.e., include the main steps used for finding the candidate keys).

2.2 Find a minimal cover of Σ. Justify your answer (i.e., include the main steps used for finding a minimal cover).

Question 3 
Consider the relation schema Appointment={Customer, Date, Staff, Room, Branch} and the following set Σ of FDs:

• Branch, Date, Staff → Room

• Customer, Date → Staff

• Branch, Date, Room → Staff

• Branch, Customer, Date → Room

Is the above relation schema Appointment in BCNF? If not, identify a BCNF decomposition for Ap- pointment. You need to include the main steps used for identifying the BCNF decomposition. Check if this BCNF decomposition is dependency preserving.

Question 4 
Consider the following database schema of a booking system.

CUSTOMER = {CustomerID, Name, Phone}

PK: {CustomerID}

EMPLOYEE = {EmployeeID, Name}

PK: {EmployeeID}

HOTEL = {HotelNo, RoomNo, Phone}

PK: {HotelNo, RoomNo}

BOOKING = {CID, EID, HNo, RNo, Date, Price}

PK: {CID, EID, HNo, RNo, Date}

FK: [CID] ⊆ CUSTOMER[CustomerID], EID ⊆ EMPLOYEE[EmployeeID],

[HNo, RNo] ⊆ HOTEL[HotelNo, RoomNo]

4.1 Answer the following question using relational algebra expressions. You are encouraged to use relational algebra expressions to represent intermediate results if needed.

[a] Find all the employees who have never helped any customer to book rooms in a hotel named “West- World” (i.e., HNo = ‘WestWorld’). List their EmployeeIDs. (1 Mark)

[b] Find all the customers who have booked exactly one hotel room on 10-01-2019. List their CustomerIDs and names. 

4.2 Optimize the following relational algebra query (Your marks will depend on how well you present the key ideas of query optimization in your answer). In addition to this, draw the query trees correspond to queries before and after your optimisation. πCustomerID,Name,Date,HNo(σ(CID=CustomerID) V (HNo=HotelNo) V (RNo=RoomNo)
V (P rice>200)(BOOKING × CUSTOMER × Hotel))

Question 5 
Consider the relation schema R={A, B, C} and the following set Σ of FDs:
• A → B
• A → C
• B → A
• B → C
• C → A
• C → B
How many different minimal covers can be derived from Σ? List all the minimal covers that you can derive from Σ.

This COMP2400: IT Assignment has been solved by our IT  experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.