/* 
    Author: Nagarajan Prabakar, 3/4/06

    Market database SQL data definition.
    No foreign key is defined since there is a cyclic dependency.
 */


/* Phase 1: Drop all three tables of Market database if they exist */

-- if Visits table already exists, remove the table
if exists
    (select *
     from sysobjects
     where id = object_id(N'Visits')
           and OBJECTPROPERTY(id, N'IsUserTable') = 1
    )
  drop table Visits
GO

-- if Sells table already exists, remove the table
if exists
    (select *
     from sysobjects
     where id = object_id(N'Sells')
           and OBJECTPROPERTY(id, N'IsUserTable') = 1
    )
  drop table Sells
GO

-- if Wants table already exists, remove the table
if exists
    (select *
     from sysobjects
     where id = object_id(N'Wants')
           and OBJECTPROPERTY(id, N'IsUserTable') = 1
    )
  drop table Wants
GO


/* Phase 2: Create all threee empty tables of Market database */

-- create a new empty table Visits
CREATE TABLE Visits 
(Customer CHAR(12)  not null,
 Store    CHAR(12)  not null,
 constraint visitspk
   primary key (Customer, Store)
)
;

-- create a new empty table Sells
CREATE TABLE Sells 
(Store   CHAR(12)   not null,
 Product CHAR(12)   not null,
 constraint sellspk
   primary key (Store, Product)
)
;

-- create a new empty table Wants
CREATE TABLE Wants 
(Customer CHAR(12)  not null,
 Product  CHAR(12)  not null,
 constraint wantspk
   primary key (Customer, Product)
)
;


/* Phase 3: Add rows of data to each of these three empty tables of Market DB */

-- Insertion of sixteen Visits rows
INSERT INTO Visits
VALUES
('Alan','Sears');
INSERT INTO Visits
VALUES
('Alan','Burdines');
INSERT INTO Visits
VALUES
('Alan','BestBuy');
INSERT INTO Visits
VALUES
('Bruce','Burdines');
INSERT INTO Visits
VALUES
('Bruce','BestBuy');
INSERT INTO Visits
VALUES
('Bruce','Macy');
INSERT INTO Visits
VALUES
('Carl','Sears');
INSERT INTO Visits
VALUES
('Carl','Macy');
INSERT INTO Visits
VALUES
('Charles','BrandsMart');
INSERT INTO Visits
VALUES
('Charles','JCPenny');
INSERT INTO Visits
VALUES
('Fred','BestBuy');
INSERT INTO Visits
VALUES
('Jane','Burdines');
INSERT INTO Visits
VALUES
('Jane','Sears');
INSERT INTO Visits
VALUES
('Maria','BrandsMart');
INSERT INTO Visits
VALUES
('Maria','Macy');
INSERT INTO Visits
VALUES
('Smith','Target');

-- Insertion of eighteen Sells rows
INSERT INTO Sells
VALUES
('BestBuy','Television');
INSERT INTO Sells
VALUES
('BestBuy','Camcorder');
INSERT INTO Sells
VALUES
('BrandsMart','Suitcase');
INSERT INTO Sells
VALUES
('BrandsMart','Refrigerator');
INSERT INTO Sells
VALUES
('BrandsMart','Phone');
INSERT INTO Sells
VALUES
('Burdines','Camcorder');
INSERT INTO Sells
VALUES
('Burdines','Dress');
INSERT INTO Sells
VALUES
('Burdines','Sofa');
INSERT INTO Sells
VALUES
('JCPenny','Suitcase');
INSERT INTO Sells
VALUES
('JCPenny','Phone');
INSERT INTO Sells
VALUES
('JCPenny','Television');
INSERT INTO Sells
VALUES
('Macy','Dress');
INSERT INTO Sells
VALUES
('Macy','Phone');
INSERT INTO Sells
VALUES
('Macy','Bedding');
INSERT INTO Sells
VALUES
('Sears','Refrigerator');
INSERT INTO Sells
VALUES
('Sears','Camcorder');
INSERT INTO Sells
VALUES
('Sears','Sofa');
INSERT INTO Sells
VALUES
('WalMart','Plant');

-- Insertion of fifteen Wants rows
INSERT INTO Wants
VALUES
('Alan','Bedding');
INSERT INTO Wants
VALUES
('Alan','Phone');
INSERT INTO Wants
VALUES
('Bruce','Sofa');
INSERT INTO Wants
VALUES
('Bruce','Dress');
INSERT INTO Wants
VALUES
('Carl','Camcorder');
INSERT INTO Wants
VALUES
('Carl','Dress');
INSERT INTO Wants
VALUES
('Carl','Sofa');
INSERT INTO Wants
VALUES
('Charles','Suitcase');
INSERT INTO Wants
VALUES
('Charles','Refrigerator');
INSERT INTO Wants
VALUES
('Fred','Television');
INSERT INTO Wants
VALUES
('Jane','Bedding');
INSERT INTO Wants
VALUES
('Jane','Television');
INSERT INTO Wants
VALUES
('Maria','Refrigerator');
INSERT INTO Wants
VALUES
('Maria','Phone');
INSERT INTO Wants
VALUES
('Thomas','Ring');

/* End of Market database script */
