This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: 7.104 DATABASE ENGINEERING I WEEK 04 – AM08 LAB / TUT SOLUTION DATABASE DESIGN USING NORMALIZATION This laboratory / tutorial exercise aims for the student to understand and gain skills on how to design database using normalization. Student also needs to create SQL Queries to check table structure and other information for designing a new database system. It is recommended for students to follow the order as listed below; SKU SKU_Description Department Buyer DeptBudgetCode 100100 Std.Scuba Tank, yellow Water Sports Pete Hansen BC-100 100200 Std. Scuba Tank, Magenta Water Sports Pete Hansen BC-100 101100 Dive Mask, Small Clear Water Sports Nancy Meyers BC-100 101200 Dive Mask, Med Clear Water Sports Nancy Meyers BC-100 201000 Half-dome Tent Camping Cindy Lo BC-200 202000 Half-dome Tent Footprint Camping Cindy Lo BC-200 301000 Light Fly Climbing Harness Climbing Jerry Martin BC-300 302000 Locking carabiner, Oval Climbing Jerry Martin BC-300 (Table SKU_DATA03) (A) SQL Server 2008 Database System 1. Login into SQL Server Management Studio 2. Open up the script SQL_SKU_DATA03_CreateTable.sql and execute it 3. Open up the script SQL_SKU_DATA03_InsertData.sql and execute it 4. Perform the Normalization Process: 1NF -> 2NF -> 3NF -> BCNF of the SKU_DATA03 (1) SKU → SKU_Description, Department, Buyer, DepBudgetCode (2) SKU_Description → SKU, Department, Buyer, DepBudgetCode (3) Department → DepBudgetCode (4) Buyer → Department (5) DepBudgetCode →Department The functional dependency 3 and 5 are the same so we can eliminate 5. Candidate Key The functional dependency 3 and 5 are the same so we can eliminate 5....
View Full Document
- Spring '11
- Relational model, Foreign key, buyer, Pete Hansen Pete Hansen Nancy Meyers Nancy Meyers Cindy Lo Cindy Lo Jerry Martin Jerry Martin DeptBudgetCode