exercise dadd

                                                                        QUESTION 1

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

Definition: A key consists of one or more attributes that can be identified in a row. Relational key  is an attribute that relates one identify with another entity in a relationship.

 Why needed : Relational key enable us to link the tables together in a database for easier data access storage.

2. List and explain 4 types of relational keys.

         i.                               i.)            Candidate key

                   ii.)            Primay key

                  iii)             Foreign key

                 iv)           Composite key

3.Based on the table given below, answer all the following questions:
BORROWER:  Table

BORROWER ID	BORROWER NAME	CONTACT NO	ADDRESS	CATEGORY	AGE	DATE OF BIRTH
01-200506-003	Ali Ahmad	03-12345678	Sentral	Student	18	02-februari
01-200506-012	Aminah Hassan	03-45725575	Bandar Baru Bangi	Student	20	15-februari

   a)      What is the name for the above table? Borrower

b)      What is the primary key for the above table? Borrower ID

c)       What is data type for borrower’s name, category, age and data of birth colums? Text,text,number,date/time

d)      How many attribute and records contains in the table? 6

e)      What is the function of field size,input mask, and default solve?

                                                                      QUESTION 2

Based on the following diagram below, draw a complete entity relationship diagram (ERD) which includes entity,attributes, relationship, cardinality constraints and key attributes.

     Report On Retail Details

Owner no: 40612

Owner name: Fatimah Abd Rahman

HOUSE CODE	ADDRESS	RENTAL RATE	TORANT CODE	TENANT NAME	START DATE	END  DATE
H0021	Sentul	RM 560	T0621	Ali	01//10/04	30/09/05
H0022	Kajang	RM 660	T0022	Aminah	01/12/04	30/11/05
H0023	Bangi	RM 850	T0623	Stephen	01/01/04	31/12/04

Attibute:                                                         

Owner _no (PK)                                       

Owner_name                                                   

                                                                       

House code (PK)

Address

Rental_Rate

Torant code (PK)

 Terant_name

 Start_ Date

 End_ date
 ENTITY:-

• owner
• house
• tenant

 

                                                                        QUESTION 3

Every day, there are five doctor is on duty in orthopaedic words. There are working on shift and doctor can give a treatment to their patient and each patient 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.                                 i)            Doctor information is DoctorID and Doctor Name

                   ii)            Patient information is PatientID, patient name, and address

                  iii)            Medicine information is medicineCode and medicine name.

                 iv)            For each treatment, it had TypeofTreatment and CostofTreatment

                   v)            For each accepted medicine, it has a Quantity and date 

Chapter 3: Entity Relationship Diagram (ERD/ER Model)

Cardinality Constraint
1:M = one to many
M:1 = many to one
M:M = many to many
1:1 = one to one

Relational Key
-primary key
-foreign key
-candidate key
-composite key


Exercise :
Draw a complete ERD diagram for the following
a) a customer reserves a dute for maintenance or repair to a vehicle  the reservation is given a reservation_no,customer_id and vehicle_no are recorded with the reservation.the time_of_reservation is also recorded
b)information stored about customer includes  customer_id,customer_name,address and telephone_no
c)information kept a about the vehicle includes vehicle_no,make,reg_no and date_of_manufacture
d)after examination,a number of jobs by recorded for the vehicle.each job has a job_no with the booking,and reasons for carrying out the job are recorded as why_needed
e)the pairs used for each job and time_spent on each job are also recorded
f)the information about parts include part_no and price

Step
1. Find the entity
2. find the attribute
3. relationship sketch ERD
4. sketch ERD relationship
5. cardinality constraint
6. attribute
7. primary key/ foreign

1. Customer
-Customer_ID(PK)
-Customer_name
-Address
-Telephone_no
2.Vehicle
-Vehicle_no
-make
-Reg_no
-Date_of_manufacture
3.Reservation
-Reservation_no
-Customer_ID
-Vehicle_NO
-Time_of_reservation
4.Jobs
-Job_no
-Why_needed
5. Parts
-Parts_No
-Price
-Time_spent

introduction to database design tools

• click on the start button
• point cursor at all program in the start menu
• move cursor to microsoft office and click on it
• click on the microsoft access 2003 or 2007

create database

• click on file button
• click on new button
• select blank database on the new file

 create table in design view

•  click on table in database window
• double click on create in design view
• field properties change field size student ID from 50 to 15
• highlight row
• add primary key

create from by using wizard

• click on form
• double click on create form by using wizard 

After the table have done, After we get the 4 tables done. We put the relationships between 4 tables. It came out like figure below

After finishing the save mode. Check all the tables,if all the tables were been arranged correctly.

Now put the data in each of the tables,

you may see a little box next to the primary key button,

there is a "+" box, press it and it will show all 3 tables 

and fill it up with all informations that been ask. 

After you have settle it up, save it.

That is all how we learn and understand learning 

about Introduction to Database Design Tools foe this week.