assn5 - Homework5 1.264,Fall2006

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
Homework 5 1.264, Fall 2006 Database implementation for chemical manufacturer Due: Thursday, October 19, 2004 A. Overview In this homework you will implement, in SQL Server Express 2005, the database that you designed in homework 4. You may use the homework 4 solutions as the guide for your database design. You may also use your homework 4 response; correct any errors in your data model before you begin building the SQL Server database. The dataset is on the course server; see the Web site for details. B. Database implementation overview The (logical) data model and (physical) database can be the same. If you make some changes between the data model and database implementation, explain them. The SQL Server database that you create must: Contain relationships among tables, to allow queries across all the data to be made conveniently. Contain referential integrity, to ensure correctness when records are added, deleted or modified. Contain appropriate keys, both primary and foreign. Be appropriately structured to support the operations required. The entities and attributes must be based on the data model from homework 4. Support appropriate cascading delete/update rules for entities with foreign keys. You must load your database with the data from the dataset provided on the course Web site. You will need to write a set of SQL queries to load the tables in your database from the original datasets provided to you. The dataset contains 6 tables, but they are not in normal form, nor do they have relationships among them. Do not build any data entry forms or menus. We will create these in the following homework sets. Also, you don’t need to create indexes for performance in this homework A set of suggested steps to develop the database are given below. You are free to approach it in other ways. The steps generally mirror those used to build the data model in homework 4. C. Database implementation NOTA BENE: Constructing a database in SQL Server is different than how Visual Paradigm builds the data model. While Visual Paradigm automatically inserted the foreign keys in child tables, SQL Server and other databases don’t do that. You must build tables with all attributes, including foreign key attributes, and then set the relationships.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Also: You must REFRESH after adding each table or relationship in the SQL Server client. Otherwise the new table or relationship may not appear. You must SAVE each new table after each modification before continuing. Last: SQL Server allows NULLs in columns by default. It’s ok to accept the default in this homework, to save some time, but it’s a bad idea to do this in general. 1. Chemical Product. a. Create the ChemicalProduct table by doing a SELECT DISTINCT column1, column2, … INTO ChemicalProduct from CHEMICAL_RAW. Use the client; type the SQL into the query window. (Hit ‘New Query’ on toolbar to get the window.) Select the correct columns from CHEMICAL_RAW; use your data model for guidance. b.
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 7

assn5 - Homework5 1.264,Fall2006

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online