Question

1. Using your downloaded DBMS (MS SQL Server), create a new database.
2. Create the database tables based on your entities defining
   1. The attributes within each table
   2. The primary and foreign keys within each table

*****Show your database tables, tables attributes, primary and foreign keys*****

Do not forget to check the lesson slides and videos that show you how to convert an ER/EER into a database schema, and how to create a database and tables using MS SQL Server.

Answer 1

QUERY

/*==============================================================*/
/* Table: Customer */
/*==============================================================*/
create table Customer (
Id int identity,
FirstName nvarchar(40) not null,
LastName nvarchar(40) not null,
City nvarchar(40) null,
Country nvarchar(40) null,
Phone nvarchar(20) null,
constraint PK_CUSTOMER primary key (Id)
)
go

/*==============================================================*/
/* Table: "Order" */
/*==============================================================*/
create table "Order" (
Id int identity,
OrderDate datetime not null default getdate(),
OrderNumber nvarchar(10) null,
CustomerId int not null,
TotalAmount decimal(12,2) null default 0,
constraint PK_ORDER primary key (Id)
)
go

/*==============================================================*/
/* Table: OrderItem */
/*==============================================================*/
create table OrderItem (
Id int identity,
OrderId int not null,
ProductId int not null,
UnitPrice decimal(12,2) not null default 0,
Quantity int not null default 1,
constraint PK_ORDERITEM primary key (Id)
)
go

/*==============================================================*/
/* Table: Product */
/*==============================================================*/
create table Product (
Id int identity,
ProductName nvarchar(50) not null,
SupplierId int not null,
UnitPrice decimal(12,2) null default 0,
Package nvarchar(30) null,
IsDiscontinued bit not null default 0,
constraint PK_PRODUCT primary key (Id)
)
go

/*==============================================================*/
/* Table: Supplier */
/*==============================================================*/
create table Supplier (
Id int identity,
CompanyName nvarchar(40) not null,
ContactName nvarchar(50) null,
ContactTitle nvarchar(40) null,
City nvarchar(40) null,
Country nvarchar(40) null,
Phone nvarchar(30) null,
Fax nvarchar(30) null,
constraint PK_SUPPLIER primary key (Id)
)
go

alter table "Order"
add constraint FK_ORDER_REFERENCE_CUSTOMER foreign key (CustomerId)
references Customer (Id)
go

alter table OrderItem
add constraint FK_ORDERITE_REFERENCE_ORDER foreign key (OrderId)
references "Order" (Id)
go

alter table OrderItem
add constraint FK_ORDERITE_REFERENCE_PRODUCT foreign key (ProductId)
references Product (Id)
go

alter table Product
add constraint FK_PRODUCT_REFERENCE_SUPPLIER foreign key (SupplierId)
references Supplier (Id)
go