DOC PREVIEW
EIU BUS 3500 - BUS 3500 Notes

This preview shows page 1-2-19-20 out of 20 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 20 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 20 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 20 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 20 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 20 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

NormalizationNormal FormsSlide 3KeysSteps Of NormalizationSlide 6Non-normalized tableSlide 8First Normal FormChecking Second Normal FormSlide 11Second Normal FormChecking Third Normal FormSlide 14Part 1 of Third Normal FormSlide 16CheckingThird Normal FormThird Normal FormDraw the ERD for ValidationNormalized Relation5-1NormalizationA technique that organizes data attributes (or fields) such that they are grouped to form stable, flexible and adaptive entities.5-2Normal FormsFirst Normal Form (1NF)There are no attributes (fields) that have more than one value for a single instance (record) of the entity.There are no repeating fieldsSecond Normal Form (2NF)1NFThe value of all non-primary key fields are dependent on the full primary key - not just part of it5-3Normal FormsThird Normal Form (3NF)2NFThe values of its non-primary key fields are not dependent on any other non-primary key field5-4KeysPrimaryA field whose values identify one and only one record in a tableEvery ACCESS table must have EXACTLY one primary keyForeignA primary key of one table that is included in another tableLinks the records in one database table to another table5-5Steps Of Normalization1. Remove repeating groups of data fields.2. Remove partial dependencies: If any data fields are dependent upon only part of the primary key defined for your core business transaction, split those fields out into a separate table, using the field(s) upon which they are dependent as the primary key.5-6Steps Of Normalization3. Remove transitive dependencies: If any fields are dependent upon a non-key field, split those fields out into a separate table, using the non-key field as the primary key. 4. Remove any field which can be easily derived from other field(s): If any fields can be derived from others, remove from database.5. Check by drawing/comparing to E-R diagram: is this model correct/logical?5-7Non-normalized tableIn this example, we want to design an order database. Identify all fields needed in this databaseWhich attribute above uniquely identifies each order?•Order Number is the starting primary key5-8Non-normalized tableAn order can have many parts. This is a repeating field. What fields above are more closely related to a part than an order? Part Number, Number of Parts [quantity ordered], Part Description, Unit Cost, Supplier Number [for the part], supplier name, supplier address. Remove these fields and put them into a separate table. Which one of these fields uniquely identifies the part information? [Part Number] The Primary Key to the new table will be the original primary key combined with the new unique identifier (Part Number)When a primary key has two or more parts, it is called a composite primary key.Original Primary Key: Order Number5-9First Normal FormPrimary Key: Order NumberComposite Primary Key: Order Number plus Part Number5-10Checking Second Normal Form Look at the second tableIf you know a part of the primary key, could you derive any of the non-keyed fields?•For example: If you know the part number [part of the primary key], could you derive the part description, the unit cost and the supplier information for that part?•Violation of second normal form Primary Key: Order NumberComposite Primary Key: Order Number plus Part Number5-11Checking Second Normal Form Remove the non-keyed fields from the second table that are dependent on only part of the primary key and put them in a new table [Part Description. Unit Cost, Supplier Information]Copy the part of the primary key from which these fields can be determined into the new table. This field will become the primary key of the new table.Primary Key: Order NumberComposite Primary Key: Order Number plus Part Number5-12Second Normal Form Primary Key: Order NumberComposite Primary Key: Order Number plus Part NumberPrimary Key: Part NumberNotice: I moved the PART table name to the table that had the most information about the part. The ordered part table name came from the rules for the ERD. One order can have many parts on it and one part [product line] can be on many orders.5-13Checking Third Normal Form Look at the third table: If you know the Supplier Number [a non-keyed field], you can determine the supplier name and address [non-keyed fields]Violation of third normal formPrimary Key: Order NumberComposite Primary Key: Order Number plus Part NumberPrimary Key: Part Number5-14Checking Third Normal Form Remove the fields that can be determined from the other field from this table. Put them [supplier name and address] in a new table. Copy the field that determines them from the original table into the new table [supplier number]. This field will be the primary key of the new table.Primary Key: Order NumberComposite Primary Key: Order Number plus Part NumberPrimary Key: Part Number5-15Part 1 of Third Normal Form Primary Key: Order NumberComposite Primary Key: Order Number plus Part NumberPrimary Key: Part NumberPrimary Key: Supplier Number5-16Checking Third Normal Form  The top table has the same violation as before but with the customer information.Customer name and address can be determined from the customer numberViolation of 3rd Normal FormPrimary Key: Order NumberComposite Primary Key: Order Number plus Part NumberPrimary Key: Part NumberPrimary Key: Supplier Number5-17CheckingThird Normal Form Remove the fields that can be determined from the other field from this table. Put them [cust name, address] in a new table. Copy the field that determines them from the original table into the new table [customer number]. This field will be the primary key of the new table. Primary Key: Order NumberComposite Primary Key: Order Number plus Part NumberPrimary Key: Part NumberPrimary Key: Supplier Number5-18Third Normal Form Primary Key: Order NumberComposite Primary Key: Order Number plus Part NumberPrimary Key: Part NumberPrimary Key: Supplier NumberPrimary Key: Customer Number5-19Draw the ERD for ValidationCustomersCust NoCust NameCust AddressPlaceOrdersOrder NoOrder DateDelivery DateOrder TotalCustomer No ContainOrdered PartOrder No Part Number Number of PartsLocateParts [Inventory]Part NumberPart DescriptionUnit CostSupplier No LocateSupplierSupplier NoSupplier NameSupplier Address11mMM11M5-20Normalized


View Full Document

EIU BUS 3500 - BUS 3500 Notes

Documents in this Course
Load more
Download BUS 3500 Notes
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view BUS 3500 Notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view BUS 3500 Notes 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?