Question

Fundamentals of Database Systems

Consider the following data. Arrows show the functional dependency Product Item ld QuantityItem Used uct Receipt Quantity Sales ription Number Num Description Date The arrows in this question indicated the determination of two attributes. For example, the arrow that goes ProductID to ProductDescription indicates that ProductID determines the ProductDescription, This in turn means that Productld can be considered as primary key for ProductDescription a) Write the tables b) Place the tables in 3rd normal form (if necessary) c) Create ERD based on the normalized tables d) Write a script to create a database. Your script should create the tables and ensures that all constraints are set properly Here is some information to create your tables DATA ITEM TYPE RESTRICTION COLUMN NAME Productlod Numeric Integer Numeric Integer Numeric Integer Character Up to 200 Character Up to 200 Numeric Integer Numeric Integer Date Not null >= 0 QuantityUsed ItemDescription Not null >=0

Answer 1

a) Create table:

Create table Product(ProductId int primary key,ItemNum int not null,QuantityUsed intCheck(QuantityUsed>=0),ItemDescription varchar(200), ProductDescription varchar(200),ReceiptNumber int not null,QuantitySold int check(QuantitySold>=0) ,SalesDate DateTime)

b) In 3rd NormalForm, divide the Product table into sub table according to its dependency

so first of all we create all the dependency tables as follows:

1. Create item table

Create table item(ItemNum int primary key, ProductId int references Product(ProductId),

ItemDescription varchar(200), QuantityUsed int Check(QuantityUsed>=0))

2. Create Receipt table

Create table Receipt(ReceiptNumber int primary key, QuantitySold int check(QuantitySold>=0),

SalesDate DateTime))

3. Create ProductQuantity table

Create table ProductQuantity(ProductId int primary key, QuantityUsed int Check(QuantityUsed>=0),

ProductDescription varchar(200), QuantitySold int check(QuantitySold>=0) )

C. ERD

D. Script for create a database and table

USE [Test]

GO

CREATE DATABASE [Test] ON PRIMARY
( NAME = N'Test', FILENAME = N'\FSASQLDBTest.mdf' ,
SIZE = 2GB , MAXSIZE = 8GB, FILEGROWTH = 1GB )
LOG ON
( NAME = N'Test_log', FILENAME = N'\FSASQLDBTest_log.ldf' ,
SIZE = 1GB , MAXSIZE = 2GB , FILEGROWTH = 10%)
GO

USE [Test]
GO

CREATE TABLE [dbo].[Product]
(   
[ProductId] [INT] PRIMARY KEY,
[ItenNum] [INT] NOT NULL,[QuantityUsed] [INT]NOT NULL,

[ItemDescription] [nchar](200)NULL,
[ProductDescription] [nchar](200) NULL,[ReceiptNumber][INT] NOT NULL,[QuantitySold][INT]
[SalesDate] DATETIME NULL
) ON [PRIMARY]
GO