Transcript Entity Relationship Diagram (ERD)

Huffman Trucking Fleet Vehicle
Maintenance Database
DBM380
June 13, 2012
Learning Team B
Learning Team B
•
•
•
•
Jorge Alvarez
Mark Miller
Dominic Rivera
Jared Wells
Entities and Attributes
• Entities and attributes define the columns of
data in the database.
• Entity- the subject of the table, i.e. “Vehicles.”.
• Attributes- properties that describe the entity,
i.e. “Mileage.”
• Together, entities and attributes become tables
organized by columns and rows.
• Each table will contain a Primary Key (PK).
• The Entities and Attributes chosen.
Entity Relationship
Diagram (ERD)
•The ERD for Huffman maintenance
shows the relationships between the
tables through Crow’s Feet notation.
•The diagram above shows the types of
relationships in the ERD organized by
the type of line connecting the tables.
•The diagram also shows the reference
tables and Primary Keys.
Database Normalization
• The types of Normalization
are:
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form
(BCNF)
o Fourth Normal Form (4NF)
o Fifth Normal Form (5NF)
o Domain/Key Normal Form
(DKNF)
o
o
o
o
• Normalization eliminates
redundancy and anomalies by
reorganizing data.
• The Huffman Fleet
Maintenance Database is
normalized to 3NF.
• Columns and data types are
unique.
• All columns rely on PK.
• All subsets of data are placed
into their own tables.
• Anything above 4NF is at
times too time-consuming and
costly.
Structured Query Language (SQL)
• In MySQL, query language is used to INSERT, UPDATE,
ALTER, DELETE, and CREATE.
▫ CREATE Command - is used to create a database/table.
▫ SELECT Command - is used to retrieve data from the
database.
▫ DELETE Command - is used to delete data from the
database.
▫ INSERT Command - is used to insert data into a database.
▫ UPDATE Command - is used to update the data in a table.
▫ DROP Command - is used to delete or drop the
database/table.
Structured Query Language (SQL)
Database Creation Query
delimiter $$
CREATE DATABASE `huffman database` /*!40100 DEFAULT CHARACTER
SET latin1 */$$
• Table Creation Queries
• Foreign Key Creation Queries
• Data Insertion and Modification Queries
Questions?
References
• Erack Network. (2008) Retrieved June 7, 2012 from
http://www.tizag.com/sqlTutorial/sqlqueries.php
• Interpreting entity-relationship diagrams. (n.d.). Retrieved
from http://www.philblock.info/hitkb/i/interpreting_entityrelationship_diagrams.html
• Kroenke, D. M., & Auer, D. J. (2010). Database concepts (4th
ed.). Upper Saddle River, NJ: Pearson/Prentice Hall.
• Stephens, R. (2009). Beginning database design solutions.
Indianapolis, IN: Wiley.