Blog Tutorial 5 - ERD

Exercise

1) Draw a complete  ER Diagram for the following:

A customer reserve a date for the maintenance / repairs to a vehicle . The reservation is given Reservation_No , Customer_ID and Vehicle_No are recorded with the reservation

The Time_Off_Reservation is also recorded. Information stored about cuatomer indudes Customer_Id , Customer_Name , Address and Telephone_No. Information kept about the vehicle includes. Vehicle_No make, Reg_No and Date_of_Manufacture.  After examination , a number of jobs are recorded for the vehicle. Each job has Job_No within the booking and reasons for carrying out the job are recorded as why_needed. The parts used for each job and the time. Spent on each job a are also recorded . The information about parts include Part_No and price.

2)a) What is the definition of relational keys and why it is needed?

Relational key consist of one or more attributes that can be identified in a row. Relational key is an attribute that relates one entity in a relationship. Relational key needed because it enables us to link the tables together in a database for easier data access storage.

b) List and explain 4 types of relational keys.

i)Candidate key-An attribute or group of attribute that identify a unique row in a relation. One of the candidate key is chosen to be Primary Key

ii)Primary key-Any of the candidate that are selected to be the key or an attribute that uniquely identifies each row in a relational table

iii)Foreign Key-An attribute in one table whose value must match the primary key in another table or whose value must not be null.

iv)composite Key-A primary key that consist of more than one attribute, especially in (M:N) relationship

c) Based on the table given above, answer all the following questions:-



d) What is the name for the above table?

BORROWER

e)What is the Primary key for the above table

BORROWER_ID

f) What is data type for the borrower’s name, category, age and date of birth columns?

BORROWER_NAME        -              TEXT

CATEGORY                      -              TEXT

AGE                                  -              NUMBER

DATE_OF_BIRTH              -              NUMBER

g) How many attributes and records contains in the table?

ATTRIBUTE          -              7

RECORDS             -              2

3)Based on the diagram below, draw a complete entity relationship diagram (ERD) which include entity attributes, relationship, cordinality constraints and key attribute

REPORT ON THE RENTAL

OWNER NO : 40012

OWNER NAME : Fatimah Abdul Rahman

4) Everyday, there are 5 doctors is on duty in Orthopedic Wards. They are working on shift A. A doctor can give a treatment  to their patients and each patients can received a treatment from many doctor based on type of diseases. A patient can take a medicine but much medicine could be taken by many patients.

i)Doctor information is Doctor_ID and DoctorName

ii)Patient information is Patient _ID, PatientName and Address

iii)Medicine information is MedicineCode and MedicineName

iv)For each treatment. It had TypeOfTreatment and CostOfTreatment

v)For each accepted medicine, it has a Quality and Date.

Create one complete ERD.



5)

Create a complete ERD which include entities, attributes, relationship, reletional keys and cordinality constraints

6)

i)How many tables exist in the database?

4

ii) how many records in the T_salesInfo tables

3

iii) How many fields consist in the T_Supplier tables?

12

iv) Is SU00A1 a valid data for Supplier in the T_Supplier tables?

Yes



7)a) What is the meaning of database and  database management system (DBMS)?

 Database is a collection of related data, designed to meet the information needed in an organization and stores it in one specific location.

Database management system is a collection of program that enable users to create and maintain a database and provide control access to the database.

   b) There are 4 types of attributes list down and give definition , example and symbol.

8)

Create Complete ERD

9) Nation warehouse is supplier that supplies many kinds of product to retailers. All stocks are supply by many manufactures depends on types of products before it is distributed to all retailers.

Currently the company is using manual system to store information on inventory and stock delivery where by the process of ordering the products only will be done when stock is reach required level and it is calculated based on quantity left at teh rack. This process is consumes a lot of time and the company decided to develop a database system to assist them in storing and updating related information. The following are requirement for the system:-

All information about manufactures need to be stored such as ManufacturerID, companyName, Address, Telephone Number and fax number. Information about retailers also need to be kept such as retailerID, companyName, address, telephone number and fax number. Product information also will be recorded such as productCode, typeOfProduct, productName, SeilingPrice, RetailPrice and Quantity in stock.

All information for each order that made to manufacturer need to be recorded such as order number, date of order, quantity ordered for each product code and overall total

All information for delivering products ordered made by manufacturer need to be kept such as delivery number, date of delivery, overall total and manufacturerID. Each delivery made can contain many kind of products and must have product code, quantity delivered for each product, delivery number and subtotal.

Information about purchasing made by retais also need to be kept such as productCode, retailerID, Quantity and subtotal. Invoice will be giving to the retailer that contains invoice number, invoice date and overall total