Database Design for Dealership and Clinic
Translated by
ScribdTranslationsDatabase Design for Dealership and Clinic
Translated by
ScribdTranslationsExercise 1.
Create a database called Dealership and carry out
the following sections:
a) Create a table named Clients with the following fields:
Data type
Field Name
Client Code Numeric
Nombre Cliente Text
Client's last name Text
Customer address Text
Population Text
Postal code Numeric
Province Text
Telephone Text
Date of birth Fecha/Hora
The primary key will be the field Client code.
b) Create another table named Sold Cars with the following fields:
Data type
Field name
Enrollment Text
Brand Text
Model Text
Color Text
Price Text
Installed extras Memo
The main key will be the Enrollment field.
c) Create another table named Revisions with the following fields:
Data type
Field name
Revision number Autonumeric
Oil change Yes/No
Change filter Yes/No
Brake revision Sí/No
Others Memo
The primary key will be the field No. revision.
Exercise 2: Create a database called Clinic and carry out the
following sections:
a) Create a table named Patients with the following fields:
Data type
Field name
Patient Code Numeric
Patient Name Text
Patient Last Name Text
Address Text
Population Text
Postal code Numeric
Province Text
Patient Phone Text
Date of birth Date/Time
We have not defined a primary key for the moment.
b) Create another table named Doctors with the following fields:
Data type
Field name
Medical Code Text
Doctor's Name Text
Last name Doctor Text
Medical Telephone Text
Specialty Text
At the moment we do not define a primary key.
c) Create another table named Income with the following fields:
Data type
Field name
Nº ingreso Autonumeric
Room Numeric
Bed Text
Fecha ingreso Date/Time
Do not define a primary key.
Exercise 3: Enter the following data into the Customers table.
Dealership database, and carry out the following sections:
Code Name Code Date
Customer Last Name Address Population Province Phone
Client Client Postal birth
100 Antonio García Pérez Shipyards, 3 Valencia 46011 Valencia 963689521 15/08/60
101 Carlos Pérez Ruiz Magallanes, 21 Utiel 46300 Valencia 962485147 26/04/58
105 Luis Rodríguez Más Juan de Mena, 11 Gandía 46700 Valencia 962965478 30/03/61
112 Jaime Juangrán Sornes Balmes, 21 Valencia 46014 Valencia 963684596 31/01/68
225 Alfonso Prats Montolla Seneca, 23 Sagunto 46500 Valencia 963547852 28/04/69
260 Joseph Navarro Lard Segura River, 14 Valencia 46002 Valencia 963874569 15/05/64
289 Elisa Ubeda Samson Valencia, 4 Sagunto 46500 Valencia 963547812 10/07/62
352 Eva San Martin Villafranca, 34 Alzira 46600 Valencia 962401589 12/08/65
365 Gerardo Luis Hernández Salinas, 8 Valencia 46002 Valencia 963589621 02/01/65
390 Carlos Prats Ruiz Ercilla, 8 Valencia 46005 Valencia 963589654 03/05/67
810 Lourdes Oliver Peris Gran Via, 34 Valencia 46007 Valencia 963587412 25/06/64
822 Sergio Larred Navas Blasco Ibáñez, 65 Valencia 46005 Valencia 963589621 25/12/67
860 Joaquín Onsins Trees Gandía, 8 Xàtiva 46800 Valencia 963758963 04/05/69
861 Joaquín Onsins Trees Gandía, 8 Xátiva 46800 Valencia 963758963 04/05/69
a) Change the name of Gerardo Hernández Luis to Alfredo.
b) Delete the last record.
Exercise 4: Clinic
a) Modify the structure of the Patients table in the clinic database following these
indications:
Field name Data type
Patient Code primary key
Province Delete this field
b) Modify the structure of the Doctors table with the following data:
Field name Data type
Medical Code Main key
Medical Phone Delete this field
c) Modify the structure of the Ingresos table with the following data:
Field name Data type
Admission number Main key
Exercise 5: Dealership
a)Modificar la estructura de la tabla Clientes de la base de datos Concesionario siguiendo los datos
that come next:
Field name Properties
Customer Code Clients with a code that is not within the range cannot be entered.
the values 1 and 3000.
Client Name Size: 15
Customer last name Size: 30
Customer address Tamaño: 30
Population Tamaño: 15
Postal code Size: 5 only accepts numbers of 5 digits and below 53000
Province Size: 15
By default, the value will be: Valencia since most of our
clients are in this province.
Telephone Size: 10 with phone mask
Date of birth Format: Short date
b) Try changing the first client's code to 4500. Notice how it doesn't allow us because it doesn't comply.
the validation rule.
c) Leave the code 100 again.
d) Check all the other properties that we have included.
e) Modify the structure of the Sold Cars table:
Nombre del campo Tipo de dato
Enrollment Size: 7
Brand Size: 15
Model Tamaño: 20
Color Tamaño: 12
Price Numeric Currency Format
Installed extras Leave the options you have
Exercise 6: Clinic III
a) Modify the Patients table in the Clinic database following these instructions:
Name of the Data type
field
Patient Code Enter
Nombre Paciente Size: 15
Patient Last Names Size: 30
Address Size: 30
Population Tamaño: 15
Postal code Size: 5
Telefono Paciente Tamaño: 10
Date of birth Format: Short date
b) Modify the Doctors table with the following fields:
Field name Data type
Medical Code Tamaño: 5
Doctor's Name Tamaño: 15
Doctor's Last Name Size: 30
Specialty Tamaño: 20
c) Modify the Incomes table with the following fields:
Field name Data type
Room Enter
Bed Size: 1
Admission date Format: Short date