Assignment 3.pdf | bartleby

1 CBD 2303 Assignment 3 Akhila Dayyala C0911186 Ankush Chauhan C0912548 Simranjeet Singh C-273 Logical Database Design You should submit by a word file, and mention all the group members who work for your submission (Question No.1 and No.3 take 10% for each, and the rest question takes 20% for each). 1. Consider the following ERD as a car hire company, and choose a Primarykey for each of the entities. Figure 1: Question 1 Ans. According to the provided ERD, each entity's main key is provided below: 1. Customer: Customer_no 2. Car: Registration_no 3. Booking: It will be a compound key including both “Customer_no” and “Registration_no”. 2. Which of the following would you do to represent the relationship belowbetween a hire CAR and CUSTOMER on the logical level? (MCQ for the first question) And what would the skeletal logical relations be for these two entities? a) put Customer no. as an attribute of CAR b) put Car reg. as an attribute of CUSTOMER c) neither d) bot h

2 Answer: a) put Customer no. as an attribute of CAR: This choice is not the greatest since it suggests keeping client information as a feature of the vehicle, which is not a recommended practise in database architecture and might result in data redundancy. b) put Car reg. as an attribute of CUSTOMER: For the same reasons as option (a), this is likewise the least desirable choice. Keeping vehicle data as a client characteristic is not a smart idea and might result in redundant data. c) neither: Most likely, this is the right response. Rather than making changes to the CAR or d) both: This isn't the ideal choice. Both entities' relationships could not be accurately represented by adding characteristics to them. The skeletal logical relations for these two entities would involve creating a third table, let's call it HIRE, with at least the following columns: Customer ID (foreign key referencing the CUSTOMER entity) Car Registration (foreign key referencing the CAR entity) Hire Date Return Date (if applicable) Other relevant hire details In order to establish a many-to-many link between CUSTOMER and CAR entities, this HIRE table would be used to record occurrences of customers renting automobiles.

3 3. Produce the logical design for the following ERD. Figure 3: Question 3 Solution 3: SUPPLIER supplier_id (PK) supplier_name supplier_description PRODUCT product_id (PK) supplier_id(FK) product_name product_description CUSTOMER customer_id(PK) customer_name customer_details product_id(FK) SUPPLIER(Supplier_no., Supplier_Name, Supplier_Address) PRODUCT(Product_Id, Description, Quantity, Price, Amount) CUSTOMER(Customer_No., Customer_Name, Customer_Address, Product_id,, Description, Quantity, Price, Amount, Tax , Delivery, Total)

4 4. A manufacturer obtains materials from a number of different supplierswhich are used to make up products. The information about the suppliers and what they can supply is shown. Figure 4: Question 4 1) Mark the column range for the embedded table(s) in the MATERIAL table above. 2) List all the attributes in tabular form (UNF). 3) Convert the data from UNF to 1NF (Like I did in our slide) 4) Draw the conceptual data model (ERD) for the two resulting relations. Solution 4:

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help