scriptFall2011

ScriptFall2011 - -One potential solution for Deliverable 3-NOTE 1 Make sure that you use the appropriate database(i.e ASPNETDB.MDF-NOTE 2 The

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

View Full Document Right Arrow Icon
--One potential solution for Deliverable 3. --NOTE 1: Make sure that you use the appropriate database (i.e., ASPNETDB.MDF) --NOTE 2: The following tables are already created in ASPNETDB.MDF: -- aspnet_Users, aspnet_Roles, aspnet_UsersInRoles -- Therefore, we do not create these three tables here. CREATE TABLE CreditCard ( CC_No varchar(16) not null primary key, FirstName varchar(30) not null, MiddleInitial varchar(1), LastName varchar(30) not null, Expiration datetime not null, CC_Type varchar(30) not null, CVC numeric(4,0) not null, isDefault bit, UserID uniqueidentifier not null references dbo.aspnet_Users(UserId) ) CREATE TABLE Discount ( DiscountID int not null primary key, LowAmount money not null, HighAmount money not null, DiscountRate decimal(2,2) not null, ModifierUserID uniqueidentifier, --we will discuss in class why we need ModifierName nvarchar(50) --these two columns in the Discount table. ) CREATE TABLE AuditDiscount ( AuditDiscountID int identity(1,1) not null primary key, AuditEvent char(6) not null, BeforeAfter nvarchar(50) not null, AuditDate datetime not null, LowAmount money not null, HighAmount money not null, DiscountRate decimal(2,2) not null, ModifierUserID uniqueidentifier references dbo.aspnet_Users(UserID), --we will discuss in class why we need these ModifierName nvarchar(50), --two columns in the AuditDiscount table. DiscountID int not null references Discount(DiscountID) ) CREATE TABLE OrderHeader ( OrderID int identity (1001,1) primary key not null, OrderDate datetime default getdate() not null, TotalAmount money not null, DiscountAmount money not null, CC_No varchar(16) not null references CreditCard(CC_No), UserID uniqueidentifier not null references dbo.aspnet_Users(UserId) ) CREATE TABLE ProductType ( ProdTypeID int identity (1,1) primary key not null, ProdTypeName varchar(30) not null ) CREATE TABLE Product (
Background image of page 1

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

View Full DocumentRight Arrow Icon
ProductID varchar(13) not null primary key, Title varchar(30) not null, ListPrice money not null, FileName varchar(255) not null, ClipName varchar(30), ProdTypeID int references ProductType(ProdTypeID) ) CREATE TABLE OrderLine ( OrderID int not null references OrderHeader(OrderID), ProductID varchar(13) not null references Product(ProductID), Quantity int not null, Price money not null, constraint OrderLine_PK primary key(OrderID, ProductID) ) --Note: the columns in the Category table are somewhat different from the one in -- the Relational schema. This is to facilitate the dynamic tree view, which -- we will discuss in class CREATE TABLE Category ( CategoryID INT Not Null primary key,
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.

This note was uploaded on 11/02/2011 for the course IDS 521 taught by Professor Staff during the Fall '08 term at Ill. Chicago.

Page1 / 5

ScriptFall2011 - -One potential solution for Deliverable 3-NOTE 1 Make sure that you use the appropriate database(i.e ASPNETDB.MDF-NOTE 2 The

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