Entity relationship diagram model of george’s garage

Introduction

Relationship between one or more then one entity of tables by pripary key and foreign key is called as Entity Relationship diagram model. This documentation will be on Entity Relationship diagram model of George’s Garage.

Database Information

Tables of the Garage Database

Garage Database gives information of the table Name with Primary key and Foreign Key.

1)Ncar_sale Pk:– Ncar_id

2).ShCar_Sale Pk:- ShCar_id

3).VehicleSale Pk: — Sale_id

Fk:- Ncar_id , ShCar_id, Dept_id

4) Car_auction Pk :– au_id

Fk:- SHCar_id

5) Car_Workshop Pk:– Workshop_id

Fk :– dept_id, Car_servise_id

6) Car_servise Pk : — Car_servise_id

FK :- Payment_id , Parts_id

7) Servise_payment Pk :– Payment_id

Fk :– Staff_id

8) Car_Parts Pk :– Parts_id

9) Customer Pk :– cust_id

10) Parts_Sale_Stock Pk :– Parts_sale_id

Fk :– parts_id, cust_id

11) Parts_order Pk :– Order_id

FK :– parts_id

12) Suplier Pk :- Suplier_id

Fk :- Order_id

13) Department Pk :- Dept_id

14) staff Pk :– Staff_id

FK :– dept_id

15) Vehicle_Booking Pk :- Booki_id

Fk :- Cust_id

16) Vehicle_Hire Pk :– Hire_id

Fk : — Booki_id, Dept_id

17) Valeting_Servise Pk :– Valeting_servise_id

FK :– booki_id,

18) Car_Hire Pk :– Car_Hire_id

19) Vehicle Pk :- Vehicle_id

Fk :– booki_id, Car_Hire_id

20) Sp_Car_Hire Pk :- Sp_Car_id

Fk :- Vehicle_id

21) Rolls_Roys Pk :– Rr_Car_id

Fk :– Sp_Car_id

22) Limousines Pk :– Li_Car_id

Fk :– Sp_Car_id

23) Minibus Pk :– Mb_Car_id

Fk :– Sp_Car_id

Entity Relationship Diagram Concepts

Keys

* Primary Key :- Primary Key is a key that uniquely identify value of the table

Example:- In payment table, Payment_id is a primary key.

Create table Servise_payment

(

payment_id varchar(5) NOT NULL CONSTRAINT carser_pk PRIMARY KEY,

)

* Foreign Key :- — Foreign Key is a key thats indicates the primary Key of the another table

Create table Car_servise

(

Payment_id varchar(5) NOT NULL CONSTRAINT spayme_fk FOREIGN KEY REFERENCES Servise_payment(payment_id)

)

* Example :- In Car_Service table, Payment_id is a foreign Key which point out another Payment_id of the Payment table

Entity

Collection of Attributes is known as Entity. In sql, parson table is a entity and Person_id, Person_name, person_Address and Person_BOD is a attributes of person.

Assume

Genaral

This database on the George garage therefore it contains all information of the garage.there are twenty three tables which are connected with each other..

a. Some of the employee have same amount of salary because they may work in same department and same position.

b. Some Cars Hire date are similar because different car might hire in same date.

c. Some tables have Integer data type or float data type due to Price of part and seat of car respectively.

Spacial

1)Ncar_sale

This Ncar tables contains detail of sales of the new cars

2).ShCar_Sale

This table store information about second hand car.

3).VehicleSale

This table keeps records of the New car and Second hand car sale. This table acts as sale department

4) Car_auction

Car auction table keeps record of car auction information. Second hand car used in car auction

5) Car_Workshop

This is Workshop department table. This table store data of car services in the workshop.

6) Car_servise

This table gives information about service of the one car. It’s also gives information about parts which are used in service, Payment and type of service.

7) Service_payment

This payment table store payment information of each Service.

8) Car_Parts

Car parts table contain details of each Parts of the car. Some car parts also order from the supplier.

9) Customer

This table store customer’s information.

10) Parts_Sale_Stock

This table is use for store information of parts sale

11) Parts_order

Parts order table contains part’s order data.

12) Suplier

This table used to store information about Supplier

13) Department

Department table contain all department information

14) staff

this table gives information of the all staff of the departsment

15) Vehicle_Hire

This table is a Hire department table which store booking information.

16) Vehicle_Booking

This table used to book hire vehicle information as well as valeting Service information.

17) Valeting_Servise

This table keeps records of the valeting service.

18) Car_Hire

Car hire tables contains information about only cars and Vans

19) Vehicle

This Vehicle table store detail of the hire vehicles such as Car and special vehicle.

20) Sp_Car_Hire

Car hire tables contains information about only special vehicle such as Minibus, Rolls Roys and Limousines.

21) Rolls_Roys

Rolls Roys table contain information about rolls roys special vehicle.Rolls Roys is used for occation

22) Limousines

Limousines table contain information about rolls roys special vehicle. Limousines used for wedding and night outs

23) Minibus

Minibus table contain information about Minibus special vehicle. This table keeps records of Airport pickup 5 Syntaxes.

Create Syntax

Create Syntax is used in sql which is for create table. Create Syntax is used in the data Definition language(DDL).

For Example :- This syntax for create Vehicle_sale table.

Create table Vehicle_Sale

(

Sale_id int(3),

New_car_sale_id varchar(5),

Secondhandcar_sale_id varchar(5),

Staff_id varchar(5)

)

Insert Syntax

Insert Syntax is used in sql which is for Insert value in the table. Insert Syntax is used in the data Manipulation language(DML).

For Example : – This syntax for insert value in the Vehicle_sale

Insert into Vehicle_Sale values(‘S01′,’Car01′,’sh01′,’em01′);

Index Systax

Index used for make search process easily and fast

For example : –

In employee table, each employ has their own phone number and it is unique