1.- For example below we have one big table. Put the table in normalized form.

SID = Student ID, S_Name= Student Name,CID = Course ID, C_Name = Course Name,

Grade = Student’s Grade in CourseFaculty = Faculty Name, F_Phone = Faculty Phone

Functional Dependencies are:

SID→S_name SID and CID→Grade CID→C_name

CID→Faculty Faculty →F_phone

The primary key of the table is SID.

Put the above table in 1NF Tables:

SID CID S_name C_name Grade Faculty F_phone

1 IS318 Adams Database A Howser 60192
1 IS301 Adams EC B Langley 45869
2 IS318 Jones Database A Howser 60192
3 IS318 Smith Database B Howser 60192
4 IS301 Baker EC A Langley 45869
4 IS318 Baker Database B Howser 60192

PK = SID,CID
Put the above table in 2NF:

GRADE (SID*,CID*,grade)

STUDENT (SID,S_name)

COURSE (CID, C_name, Faculty, F_phone)

Put the above table in 3NF Tables:

GRADE (SID*,CID*,grade)

STUDENT (SID,S_name)

COURSE (CID, C_name, Faculty*)

FACULTY (Faculty, F_phone)

Final set of Tables with meaningful names and PKs and FKs:

2.- For the example below we have one big table. Put the table in normalized form.

OID = Order ID, O_Date= Order Date,

CID = Customer ID, C_Name = Customer Name, C_State = Customer’s State,

PID = product id, P_Desc =Product Name, P_Price = Product Price, Qty = Quantity
Purchased Note: 7, 5, 4 means three Product IDs. Similarly, 1, 1, 5 means three Quantities.

Functional Dependencies are:

OID→O_Date CID→C_Name PID→P_Desc PID→P_Price

OID→CID CID→C_State PID and OID→Qty

The primary key of the table is OID.

Put the above table in 1NF Tables:

OID O_Date CID C_Name C_State PID P_Desc P_Price Qty

1006 10/24/09 2 Apex NC 7 Table 800 1
1006 10/24/09 2 Apex NC 5 Desk 325 1
1006 10/24/09 2 Apex NC 4 Chair 200 5
1007 10/25/09 6 Acme GA 11 Dresser 500 4
1007 10/25/09 6 Acme GA 4 Chair 200 6

PK = OID,PID
Put the above table in 2NF:

QUANTY (OID*, PID*, Qty)

ORDER (OID, O_Date,CID, C_Name, C_State)

PRODUCT (PID, P_Desc,P_Price)

Put the above table in 3NF Tables:

QUANTY (OID*, PID*, Qty)

ORDER (OID, O_Date,CID*)

PRODUCT (PID, P_Desc,P_Price)

CUSTOMER (CID, C_Name, C_State)

Final set of Tables with meaningful names and PKs and FKs:

3.- For the example below we have one big table representing a company’s data on their
projects and employees. Put the table in normalized form.

DID = Department ID, Dname = Department Name

EID = Employee ID, Ename = Employee Name, Btime = Budgeted Time

PID = Project ID, Pname = Project Name

Functional Dependencies are:

DID→Dname EID→Ename EID and PID→Btime

EID→DID PID→Pname

Put the above table in 1NF Tables:

DID Dname EID Ename PID Pname Btime

10 Finance 1 Huey 27 Alpha 4.5
10 Finance 5 Dewey 25 Beta 3
10 Finance 11 Louse 22 Gamma 7
14 R&D 2 Jack 26 Pail 8
14 R&D 4 Jill 21 Hill 9

PK = EID, PID
Put the above table in 2NF:

BUDGETED (EID*, PID*, Btime)

EMPLOYEE (EID, Ename, DID, Dname)

PRODUCT (PID, Pname)

Put the above table in 3NF Tables:

BUDGETED (EID*, PID*, Btime)

EMPLOYEE (EID, Ename, DID*)

PRODUCT (PID, Pname)

DEPARTAMENT (DID, Dname)

Final set of Tables with meaningful names and PKs and FKs:

4.- Produce the Third Normal Form of this document by normalization.

Order number = OID, Customer number = CID, Customer name = C_name,

Customer address = C_address, ProductNo = PID, Description = P_desc,

Quantity = Qty, Unit Price = P_Price

Functional Dependencies are:

OID→Date CID→C_Name CID→City CID→C_address

PID→P_Desc PID→P_Price OID→CID City→Country

PID and OID→Qty

Put the above table in 1NF Tables:

OID CID C_name C_address City-Country PID P_desc Qty P_price

1234 9876 Billy 456 HighTower Street Hong Kong, China A123 Pencil 100 3.00
1234 9876 Billy 456 HighTower Street Hong Kong, China B234 Eraser 200 1.50
1234 9876 Billy 456 HighTower Street Hong Kong, China C345 Sharpener 5 8.00

PK = PID, OID

Put the above table in 2NF:

QUANTITY (PID*, OID*, Qty);

ORDER (OID, Date, CID, C_name, C_address, City, Country);

PRODUCT(PID, P_desc, P_price);

Put the above table in 3NF Tables:

QUANTITY (PID, OID, Qty);

ORDER (OID, Date, CID*);

PRODUCT (PID, P_desc, P_price);

CUSTOMER (CID, C_name, C_address, City*);

CITY (City, Country);

Final set of Tables with meaningful names and PKs and FKs:

5.- Do the relational model in 3NF:

QUANTITY_PROCEDURE (Pet_ID*, Owner*, Qty_Proce);

PET (Pet_ID, Pet_name, Pet_type, Pet_age, Procedure);

CUSTOMER (Owner, Visit_date );

6.- Do the relational model in 3NF:

QUANTITY_PROCEDURE (IID*, PID*, Qty_Proce);

INVOICE (IID, I_name, I_date, CID*);

CUSTOMER (CID, C_name, C_address, City, C_CP);

PET (PID, P_name, P_Procedure*);

PROCEDURE (P_Procedure, Amount);

7.- Do the relational model in 3NF:

Gallery Customer History Form

The Gill Art Gallery wishes to maintain data on their customers, artists and paintings. They
may have several paintings by each artist in the gallery at one time. Paintings may be
bought and sold several times. In other words, the gallery may sell a painting, then buy it
back at a later date and sell it to another customer.

QUANTITY (PID*, AID*, Qty);

CUSTOMER (CID, C_name, C_phone, C_address, City, C_CP);

ARTIST (AID, A_name);

PAINTING (PID, P_title, P_price, P_purchase*, CID*,);

PURCHASE (P_purchase, Pur_date);

8.- Do the relational model in 3NF:

Good News Grocers

User View 1 - Price Update List

This report is used by the department managers to update the prices that are displayed in
the grocery store for these products.

AISLE (DID*, Aisle_Number);

DEPARTAMENT (DID, PID*);

PRODUCT (PID, P_price, P_unit_of_measure);

9.- IESFBMOLL Computing wants to computerize their invoice system:

Do the relational model in 3NF.

QUANTITY (Invoice_number*, Item_code*, Qty, Qty_price);

INVOICE (Invoice_number, Invoice_name, Date, C_NIF*);

CUSTOMER (C_NIF, C_name, C_address, C_CP, Town, C_phone);

ITEM (Item_code, I_category, I_description, I_price);

10.- Normalize up to 3NF the relation R (A, B, C, D, E, F) in which the following functional
dependencies are given:

● E→B

● C→A

● C, E ⇒ F

● A →D

R1 (C*, E*, F);

R2 (C, A*);

R3 (A, D);

R4 (E, B);
11.- Normalize up to 3NF the relation R (A, B, C, D, E) in which the following functional
dependencies are given:

● C, E ⇒ D

● D→B

● C →A

R1 (C*, E*, D*);

R2 (D, B);

R3 (C, A);

12.- Do the relational model in 3NF.

Entity-relationship model:

RENT (C_number*, M_code*, rent_date, return_date);

CUSTOMER (C_number, Name, Surname, Address, Postal_code, Town, Telephone, E-

mail);

MOVIE (M_code, Title, Director, Year, Genre);

13.- Normalize up to 3NF.

It is interesting to keep the following information:

● For each department: department number (unique), budget, and the number of the
employee who is the department manager (unique).

● For each employee: employee number (unique), current project number, office
number, personal telephone number, name of each job that has been held, description of
each job, and amount and range of dates of each salary which he has received for each of
these works.

● For each project: project number (unique) and budget.

● For each office: office number (unique), area in m2, and numbers (unique) of all the
phones in this office.

----------------------------

● Para cada departamento: número de departamento (único), presupuesto y número del

empleado que es el director del departamento (único).

● Para cada empleado: número de empleado (único), número de proyecto actual, número
de oficina, número de teléfono personal, nombre de cada trabajo que ha tenido, descripción
de cada trabajo y cantidad y rango de fechas de cada salario por el que ha recibido cada
una de estas obras.

● Para cada proyecto: número de proyecto (único) y presupuesto.

● Para cada oficina: número de oficina (único), área en m2 y números (únicos) de todos los
teléfonos de esta oficina.
14.- Indicate the normal form of the following relationships and normalize them if necessary:

Indique la forma normal de las siguientes relaciones y normalícelas si es necesario:

TRIPS (id_trip, departure_time, date, vehicle_plate, driver_id_card, driver_surname)

With the next functional dependencies:

● FD1: {id_trip, departure_time} → {vehicle_plate} → {driver_surname}

● FD1: {driver_id_card} → {driver_surname}

15.- An enterprise works with the following sheet:

Customers (Customer id, Name, Surname, {Phone}, Address)

Translate to the 1NF, 2NF, and, finally, to the 3NF.

1NF

Customer_id Name Surname Phone Address

1 Brad Pitt 90000001 504 Avenue, 23
1 Brad Pitt 60012312 504 Avenue, 23
1 Brad Pitt 61132199 504 Avenue, 23
2 Jennifer Lawrence 97100012 525 Avenue, 3
2 Jennifer Lawrence 97100013 525 Avenue, 3
3 George Clooney 60012345 104 Avenue, 53
4 Jane Fonda 97111111 14 Avenue, 2
4 Jane Fonda 97122222 14 Avenue, 2

2NF

3NF
16.- An enterprise works with the following sheet:

As you can see, a project can have many customers and a customer can have many
projects. Every project has a single person in charge (and obviously that employee may be
in charge of many projects).

Como puede ver, un proyecto puede tener muchos clientes y un cliente puede tener muchos
proyectos. Cada proyecto tiene una sola persona a cargo (y obviamente ese empleado
puede estar a cargo de muchos proyectos).

CustomerProjects (Customer id, Name, Surname, Project, Budget, In charge, State)

Translate to the 1NF, 2NF, and, finally, to the 3NF.

1NF

Customer_id Name Surname Project Budget In charge State

1 Brad Pitt Website12 12500 SGR 1
2 Jane Fonda DigiATM 50000 DGF 3
3 George Clooney DigiATM 49000 DGF 3
4 Brad Pitt App15 25000 SGR 5

2NF

CUSTOMER_PROJECT (Customer_id*, Project*)

CUSTOMER (Customer_id, Name, Surname);

PROJECT (Project, Budget, In_charge, State);

3NF
17.- A shop rents video games. They work with a sheet with the following fields:

RENT (Videogame id, Title, Copy number, Customer num, Customer name, Customer
phone number, rent date, rent number days)

Translate to the 1NF, 2NF, and, finally, to the 3NF.

18.- A college keeps data from students, professors, studies and subjects (inside those
studies). The following considerations are assumed:

● A student must be enrolled in a single career (and maybe he/she is not enrolled in all
the subjects of that career).

● There are subjects shared in different studies.

● A professor teaches a concrete subject (and he/she can teach many subjects).

-----------------------------

Una universidad guarda datos de estudiantes, profesores, estudios y materias (dentro de

esos estudios). Se asumen las siguientes consideraciones:

● Un estudiante debe estar matriculado en una sola carrera (y tal vez no esté matriculado
en todas las asignaturas de esa carrera).

● Hay temas compartidos en diferentes estudios.

● Un profesor enseña una materia concreta (y puede enseñar muchas materias).

They work with a sheet like this ones:

Translate to the 1NF, 2NF, and, finally, to the 3NF.