Database Normalization & Implementation

EMPLOYEE (EmployeeID, SSN, EmployeeFirstName,EmployeeLastName,EmployeePhone,DepartmentID, DepartmentName,DepartmentPhone,DepartmentAddress) Business Rules: Employee works in one department. A department can have many employees Step 2:Identify functional dependencies

1. DepartmentName,DepartmentPhone,DepartmentAddress

EMPLOYEE (EmployeeID, SSN, EmployeeFirstName,EmployeeLastName,EmployeePhone,DepartmentID, DepartmentName,DepartmentPhone,DepartmentAddress) Business Rules: Employee works in one department. A department can have many employees Step 1:Identify all candidate key

1. EmployeeID 2. SS

What form occurs when all columns that are in the table that are not functionally dependent on the primary key should be remove

3NF

What is the final normalized relations in BCNF of EMPLOYEE (EmployeeID, SSN, EmployeeFirstName,EmployeeLastName,EmployeePhone,DepartmentID, DepartmentName,DepartmentPhone,DepartmentAddress) Business Rules: Employee works in one department. A department can have many employees

EMPLOYEE(EmployeeID underlined,SSN, EmployeeFirstName,EmployeeLastName,EmployeePhone, DepartmentID italicized) DEPARTMENT(DepartmentID , DepartmentName, DepartmentPhone, DepartmentAddress) also create the referential integrity rule 1. DepartmentID in EMPLOYEE must exist in DepartmentID in DEPARTMENT

The _______ is more mostly a framework and it gives us the blueprint for us to implement the database structure in the database management system.

ER model

Normalization focuses on making the database Versatile End user friendly Effective Standardized

Effective

What is the special case of 3NF?

BCNF (Boyce-Codd Normal Form)

What are the steps of the BCNF normalization process

BCNF normalization process Step 1: Identify all the candidate keys in a relation Step 2: Identify all the functional dependencies in a relation Step 3: Verify the determinants of the functional dependencies, if any of the determinants is not a candidate key the relation is not in BCNF normalized form. In this situation ◦ Place the columns of the functional dependency in a new table of its own ◦ Make the determinant of the functional dependency the primary key of the relation ◦ Leave a copy of the determinant as a foreign key in the original relation ◦ Establish referential integrity constrain

A functional dependency is a relationship between or among A. Entities B. Rows C. Attributes D. Tables

C. Attributes

The set of attributes in a given relation that defines all the other attributes. There can be more than one

Candidate key

Is more like the blueprint and the big picture E-R model normalization

E-R model

The only requirement for a relation to be in 2NF is that table cells should contain only single atomic values T/F

F

The relationship between attributes in an entity in which one attribute or a group of attributes can determine the value of another attribute in the same table

Functional Dependency

An attribute or set of attributes that defines a set of attributes in a relation

Functional dependencies

The first step in the normalization process is to Identify the primary key Identify all functional dependencies Identify all the candidate keys in the relation Identify the foreign keys in the relation

Identify all the candidate keys in the relation

If you have company and it's in italics what does this mean

It will represent the foreign key

What is normalization

Normalization = Is the process of seperating data into logical tables. is a branch of relational theory and it provides design insights.

When is a relation in BNCF?

Only if every functional determinant in the relation is a candidate key

For the relation below identify a functional determinant Prescription(PrescriptionID, MedicineName, MedicineCategory, Date) PrescriptionID MedicineName MedicineCategory Date

PrescriptionID

_______ key in a table can functionally determine all the other attributes in a table

Primary

________ keys are underlined. ______ key is denoted in italics

Primary Foreign

The foreign key record values must correspond to a matching value of the PK in the table that the FK is referenced to

Referential Integrity

EMPLOYEE (EmployeeID, SSN, EmployeeFirstName,EmployeeLastName,EmployeePhone,DepartmentID, DepartmentName,DepartmentPhone,DepartmentAddress) Business Rules: Employee works in one department. A department can have many employees What will need to happen to make this into BCNF?

So we split the functional dependency to a relation of its own DEPARTMENT ( DepartmentID , DepartmentName, DepartmentPhone,DepartmentAddress) We then leave a copy of DepartmentID in the original relation as a foreign key We do not pick SSN as the primary key for security reasons

STUDENTID -->(StudenLastName, StudentFirstName, Major, G.P.A) Which is the functional determinant?

Student Id b/c of all the other attributes that you're keeping track of a student

StudentID(StudentName, StudentEmail, MentorID, MentorName, Mentor Email) Business Rule: A student has only one mentor, however a mentor can advise many students Identify the functional determinant (Hint: think about attributes that can determine the other attributes)

StudentID

A relation is in BCNF if, and only if every functional determinant in the relation is a candidate key T/F

T

every foreign key is referencing to a primary key in the table that it's related to T/F

T

redundant data in waste space, and it can also have maintenance issues T/F

T

the price of one t-shirt can determine the price of ten t-shirts. What is the determinant?

T-shirt price and quantity together (TshirtPrice, Quantity)

COMPANY(CompanyID, Name, Address, CompanyContact) List the Table Name, Primary key, atributes

Table name- COMPANY Primary key is underlined- CompanyID Attributes- Name, Address, CompanyContact

For the relation below identify a functional determinant CAR(Vin, Make, Model, Year) Year Make Model Vin

Vin

For the relation below identify a functional determinant based on the business rule CAR(Vin, Make, Model, Year, LocationId, LocationType, LocationAvailability) Business Rule: A car can only be at one location. However a location can have many cars LocationType Make LocationAvailability Vin

Vin

EMPLOYEE (EmployeeID, SSN, EmployeeFirstName,EmployeeLastName,EmployeePhone,DepartmentID, DepartmentName,DepartmentPhone,DepartmentAddress) Business Rules: Employee works in one department. A department can have many employees Step 3: Is there a determinant that is not a canindate key

Yes- DepartmentID is a determinant and not a candidate key

A candidate key can be a __________

composite key

If we have a one to one, we can place the foreign key where?

either one of the two entities

What is First Normal Form 1NF?

eliminate repeating groups in individual tables. So the table cells should should contain single atomic value

normalization theory relies on the concept of ____________.

functional dependency

We call the attribute that determines the other attributes as a _____________________

functional determinant

If we have a one to many, we are always going to place our foreign key where?

in the many side of the entity.

the foreign key placement really depends upon the ___________cardinality minimum maximum

maximum

What is Second Normal Form 2NF?

needs to meet the first condition of being in first normal form. And in addition, the records need to be uniquely identified with a primary key or we need to have a unique identifier within each record that would be able to identify the records in that particular table.

____________ is more focused on specific entities and also focused on making our database more effective E-R model normalization

normalization

Where are you going to place the foreign key if we have a many-to-many,

we need to create a new associative entity.