Formal Report: Database Structure, Normalization, and Entity Modeling

This report provides a comprehensive analysis of database structure, normalization, and entity modeling. It begins with an understanding of an existing database structure, detailing tables with their attributes. The report then delves into normalization, using dependency diagrams to identify and eliminate partial and transitive dependencies. This process leads to the decomposition of the initial table into multiple normalized tables, with corresponding SQL queries provided to demonstrate data retrieval from each table. Furthermore, the report proposes an entity model, including entities such as Entity_Performance, Entity_Work, and Entity_Type of Work, along with their attributes and relationships. The report also models the relationships between these entities using different notations like m..n, n..1 and 1..n. The overall goal is to create a well-structured and efficient database design.

Formal Report
1. Understanding an existing database structure
a. Student (Stno, Sname, Major, Class, Bdate);
Grade_report (Student_Number, Section_ID, Grade);
Section (Section_ID, Course_Num, Semester, Year, Instructor, Bldg, Room);
Department_to_major (Dcode, Dname);
Prereq (Course_Number, Prereq);
Course (Course_Name, Course_Number, Credit_Hours, Offering_Dept);
Cap (Name, Language);
Room (Bldg, Room, Capacity, Ohead);
Dependent (Pno, Dname, Relationship, Sex, Age);
b. Entity relationship diagram

2. Normalising a bulk data set
a. Dependency diagrams
 <Model, Style, Engine> --> Price
The candidate key is the combination of model, style and engine attributes which
determines the price. A combination of model, style and engine can be the primary key
because for every unique combination of model, style and engine, there is unique price.
 <Model, Style> --> Market --> Competitor
The candidate key is the combination of model and style which determines the market and
then the market
Is a candidate key that determines the competitor. The combination of model and style can
be a primary key because for every unique model and style there is a unique market. The
market can be a primary key because for every market
there is a unique competitor according to the data provide in the table.
 <Model, Engine> --> Max Speed
The candidate key is the combination of model and engine attributes which determine the
MaxSpeed. The model and engine can be a primary key because for every unique
combination of model and engine there is a max speed
 <Engine> --> Capacity

The candidate key is the engine which determines the capacity. The engine can be a primary key
because every capacity has a unique engine according to the data in the table.
b. Based on the dependency diagrams shown above normalization results to the
following tables.
 Table_1 (model, style, engine, price)
This is decomposed to form a table because there exists partial
dependency as shown in the first dependency diagram. Eliminating the
partial dependency leads to formation of a new table.
The following sql query shows the new table.
Select price from table_1 where model=’macho’ AND
style=’convertible’ and engine=’2900s’;
This query returns £12900.
 Table_2 (Model, style, market)
This is table is decomposed from the main table because there exists
partial dependency as shown in dependency diagram 2 above.
Elimination of the partial dependency results to this table. The following
SQL query can be used to demonstrate this table.
Select market from table_2 where model=’macho’ and
style=’convertible’;
This query returns sports as the result.
 Table_3 (Market, competitor)
According to dependency diagram 2 above, the market determines the
competitor thus this transitive dependency can be eliminated to form a
new table. The following SQL demonstrates the new table;
Select competitor from table_3 where
market=’sports’;
This query returns expresso as the result
 Table_4 (Model, Engine, MaxSpeed)
According to the third dependency diagram there exists a partial
dependency and elimination of this partial dependency results to this
new table. The following SQL can be used to demonstrate the new table.
Select MaxSpeed from table_4 where Model=’macho’
AND Engine=’2900s’;
This query returns 155 as the result.
 Table_5 (Engine, capacity)
According to the last dependency diagram there exists transitive
dependency and eliminating the transtive dependency results to this new

table. The following SQL query can be used to demonstrate the new
table.
Select capacity from table_5 where Engine=’2900s’;
This query returns 2847 as the result.
3. Developing a potential entity model
a) Entities, Relationships and Attributes
Entities:
Entity_Performance
Entity_Work
Entity_Type of Work
Entity_Orchestra
Entity_Conductor
Entity_Soloist
Attributes
Title
Catalogue Number
Composer
Size of Orchestra
No. of Solo Instruments
Performance Date
Hall
Conductor Name
Soloist
Soloists Name
Relationships
Entity_Work—Reltaed to-- Entity_Performance, Entity_Type of Work
Entity_Type of Work— Reltaed to -- Entity_Work
Entity_Orchestra— Reltaed to -- Entity_Performance
Entity_Conductor— Reltaed to -- Entity_Performance
Entity_Soloist— Reltaed to -- Entity_Performance
b) Model Design