SQL view assignment

COP4703/ESI4620: IT Database Systems

Project 02

Database Analysis and Design Implement the initial database design for the Customer Relationship Management VIEW as described below in your selected RDBMS. You need to

create the Entity-Relationship model from your normalization and Database analysis, define tables, primary keys, foreign keys, create additional

descriptive fields (at least 2 for each entity – relationship tables do NOT need additional fields in all cases), and any attribute value constraints you deem

necessary. Create the VIEW and populate the tables to duplicate the following:

Sales Rep Customer Contact Name Contact Date/Time Last Order ID Last Order Date/Time

Customer Since

Credit Class

Sales Area

APL Cust 1 Sam Smith 4/2/2014 11:28 PO201403011 3/31/2014 15:32 1987 A NE

ITDB Cust 2 Susan Day 1/28/2014 08:43 PO201401201 1/20/2014 18:24 2008 AA SE

ITDB Cust 1 Susan Day 4/2/2014 11:28 PO201402001 2/3/2014 09:15 1987 A MW

APL Cust 3 Tim Collar 4/15/2014 14:03 2014 B- SE

SDCC Cust 4 Tom Jones 12/13/2013 16:40 PO201312241 3/10/2014 15:22 2010 B NE

You will be evaluated on:

1. The Feasibility, Flexibility, and Appropriateness of the Normalization and Relationships you create between entities, including indication of the

type of relationship (20 points)

Submit your Entity-Relationship Diagram generated by the DBMS in file named:

lastname.firstname.Project 02 ERD.jpg (or png or pdf or gif)

2. Definition of the VIEW, including the conditions for data population into the normalized data tables (20 points)

Please run a SELECT *FROM <view>; saving the results in a notepad (text) file with the extension .dat , and submit your file(s) using your last

name as part of the file name in the following format:

lastname.firstname.Project 02 view.dat

3. Data Population for each table supplying the view and your additional descriptive fields (10 points)

Please run a SELECT *FROM <table>; for each table, saving the results in a notepad (text) file with the extension .dat , and submit your file(s)

using your last name as part of the file name in the following format:

lastname.firstname.Project 02 tables.dat

4. The Attributes and KEY fields you identify for each entity (10 points)

COP4703/ESI4620: IT Database Systems

Project 02

5. Value Constraints you define for fields you select (20 points)

Please save all of your EXPORT DATABASE CREATE code in a notepad (text) file with the extension .sql , and submit your file(s) using your last

name as part of the file name in the following format:

lastname.firstname.Project 02 create.sql

6. Supply the SQL and result for the following action:

INSERT a new Customer and Contact record INTO THE VIEW for ServiceRep = “ITDB” where the Customer is “SPRING 2014” and the Contact

person is your name. (20 points)

Please save all of your INSERT SQL code in a notepad (text) file with the extension .sql, and submit your file(s) using your last name as part of the

file name in the following format:

lastname.firstname.Project 02 Insert.sql

Please run a SELECT *FROM <table>; for each table, saving the results in a notepad (text) file with the extension .dat , and submit your file(s)

using your last name as part of the file name in the following format:

lastname.firstname.Project 02 tables after update.dat