MIS 6309, Prof. A. Lahiri Team Homework #2
-------------------------------------------------

1. The answers are as follows: a) E-R diagram: M
Location
Positions
Industry
Has
Located in
Belongs to
M
1
M
Employer
1
1
M
Location
Positions
Industry
Has
Located in
Belongs to
M
1
M
Employer
1
1

b) Physical Layout (primary keys underlined, foreign keys double-underlined): Position PositionID int PositionTitle varchar(50) EmployerID varchar(5) Wage Money HoursPerWeek int Experience bit Openings int

Employer EmployerID varchar(5) EmployerName varchar(50) StreetAddress varchar(50) City varchar(50) StateProv varchar(2) PostalCode varchar(7) Country varchar(50) ContactFirstName varchar(50) ContactLastName varchar(50) Position varchar(50) Phone varchar(10) Website bit NAICSCode varchar(6) Comments varchar(255), must allow NULL

NAICS NAICSCode varchar(6) NAICSDesc varchar(50)

Location
Abv varchar(2)
Location varchar(50) c) Create table statements:
CREATE TABLE Location( Abv varchar(2) PRIMARY KEY NOT NULL, Location varchar(50) NOT NULL
);

GO

CREATE TABLE NAICS( NAICSCode varchar(6) PRIMARY KEY NOT NULL, NAICSDesc varchar(50) NOT NULL
);

GO

CREATE TABLE Employer( EmployerID varchar(5) PRIMARY KEY NOT NULL, EmployerName varchar(50) NULL, StreetAddress varchar(50) NULL, City varchar(50) NULL, StateProv varchar(2) NULL,
CONSTRAINT FK_State FOREIGN KEY (StateProv) REFERENCES Location(Abv), PostalCode varchar(7) NULL, Country varchar(50) NULL, ContactFirstName varchar(50) NULL, ContactLastName varchar(50) NULL, Position varchar(50) NULL, Phone varchar(10) NULL, Website [bit] NOT NULL, NAICSCode varchar(6) NULL,
CONSTRAINT FK_NAICS