Entity relationship diagram model of george’s garage

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 Georges 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. Its 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 customers information. 10) Parts_Sale_Stock This table is use for store information of parts sale 11) Parts_order Parts order table contains parts 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