CIS 4600 Business Database Applications Homework 2 [Due date: 2/14/20191 Using Microsoft SQL Server Databasel Spring 2019 This assignment involves creation of database tables, fields, primary keys (PKs) foreign keys (FKs). In addition, you will create records on your SQL Server Database tables When you create fields, you will need to specify Data Types for each field Different fields have different data types. For your convenience, common SQL Data Types are provided below: Common SQL Server Data Types Type CHAR VARCHAR Description Fixed length character stri Variable length character string nteger numb Decimal values Dates and Times DATE Note: There are two parts in this assignment. 1. Please complete both parts. 2. Copy and paste codes, results, and any other information that you need to provide on a single word document. 3. Post the word document on eLearning on or before the due date CIS4600 Spring 2019 Dr. Razi Page 1 Part A A Sample Database [Employee Database] Script is provided below: Code for Creation of Tables where Fields and Primary Keys are defined (Keyword CONSTRAINT. CREATE TABLE emplevel (LevelNo Lowsalary HighSalary Decimal) INT PRIMARY KEY Decimal, CREATE TABLE position (PositionId INT PRIMARY KEY PosDes VARCHAR (20)) CREATE TABLE qualification (QualId QualDesc INT PRIMARY KEY VARCHAR (28)) CREATE TABLE dept (DeptId DeptName Location Employeeld INT) INT PRIMARY KEY VARCHAR (12) NOT NULL ARCHAR (15) Code for Creation of Tables where Fields, Primary Keys, and Foreign Keys are defined (Keyword CONSTRAINT) Note that FOREIGN KEYalso establishes referential integrity CREATE TABLE employee Enployeeld INT PRIMARY KEY Lnane F nan@ Positionid INT Supervisor INT HireDate Salary Commission Decimal, DeptId QualId CONSTRAINT employee positionid fk FOREIGN KEY (PositionId) VARCHAR (15) NOT NULL VARCHAR (15) NOT NULL DATE Decimal, INT NOT NULL INT, REFERENCES position (PositionId), REFERENCES dept (DeptId), REFERENCES qualification (QualId)); CONSTRAINT employee deptid fk FOREIGN KEY (DeptId) CONSTRAINT employee-qualid-fk FOREIGN KEY (Qual1d) CIS4600 Spring 2019 Dr. Razi Page 2 CREATE TABLE dependent (Employeed INT PRIMARY KEY DependentId INT DepDOB Relation CONSTRAINT dependent employeeid fk FOREIGN KEY (Employeeld) DATE ARCHAR (8) REFERENCES employee (Employeeld)) Adding Foreign Key after creation of table [ALTER TABLE command is used]. ALTER TABLE employee ADD CONSTRAINT enployee supervisor fk FOREIGN KEY(Supervisor) REFERENCES employee(Employeeld) What to Turn in For HW2 Part A? Based on SQL Code above] 1. Create a word document named 2MRazi. Replace MRazi with your First Initial and Last Name. Provide following on the word document: Identify Tables, fields, PKs, and FKs based on the SQL Code in Part A. As you know database Tables are Entities and Fields are Attributes. Create an Entity Relation Diagram (ERD) based on the information (SQL Code in Part A). No need to show all attributes, just show PKs, and FKs for each relation (entity), relationships between entities, and cardinalities on both sides of the relation. Part B Diagram on page 4 represents ERD of Sales Database. Tables, fields, and data types of each field are also provided below for reference Part B Deliverables Use SQL Query Editor to create all tables, relationships, Primary & Foreign keys in YOUR Database on my server (141.218.104.41). Remember to copy-and-paste all SQL code on HW2 MRazi word document as Part B deliverables. For SQL DDL code help, look at Part A code examples. Note:A Foreign Key (FK) must have the same data type and length of the Primary Key of related parent table. After you have created all table and table relationships, it's time to create at least 3 records for each table. I will leave the records creation part to you. Warning: don't forget to follow a particular sequence of tables while creating records. CIS4600 Spring 2019 Dr. Razi Page 3
