IDS 410 – Business Database Technology Spring 2017 Individual Assignment

Subject: Business    / Accounting
Question
Physical Schema for a Simple Restaurant System
Assigned Date: Thursday, February 23, 2017
Due: Submit your solution files (two files) on Blackboard by 11:59PM on Thursday, March 9, 2017.
Problem Statement
For this assignment, you need to use a relational schema for the Lambs-Are-Us Restaurant System database, which I have included on the last
page. This relational schema has 20 logically related tables. Your tasks are as follows:
1. Create a new Microsoft ACCESS database file. Use your UIC Netid as the name of this database file. That is, if your UIC email address is
yourNetid@uic.edu, you should name your database file yourNetid. Note that ACCESS will automatically append a file type accdb. Thus,
for this example, the complete name of your database file would be yourNetid.accdb.
2. For each table in the relational schema, you need to run a CREATE TABLE statement that will create a table in your new database file. For
example, to create the CUSTOMER table (see the relational schema on the last page), you need to run the CREATE TABLE statement as
follows (use your judgment to specify an appropriate data type for each column).
Create Table Customer
(CustomerID AutoIncrement Not Null,
Name
Varchar(40) Not Null,
Phone
Varchar(20),
Email
Varchar(40) Not Null,
Primary Key(CustomerID)
);
After you run the above statement, you should see that the Customer table is created in your new database file. At this point, copy the
CREATE TABLE statement and paste it into a new script (text) file. Name this script file yourNetid.txt, where yourNetid is your UIC
Netid.
3. Since there are 20 tables in the relational schema, you need to repeat the CREATE TABLE statement 20 times. Thus, after creating the last
table, you should have 20 tables in your new database file. Moreover, you should have 20 CREATE TABLE statements in yourNetid.txt
file. Once you have created those 20 tables in your database file, click the Database Tools ribbon tab and then click the Relationships
button. At this point, you should see that behind the scene, Microsoft Access has automatically created a relational schema based on these
20 new tables.
4. Be sure to save both your database file and your script file. You need to submit both of these files to the Blackboard by the due date. Note: Use your judgment to specify an appropriate data type for each field (column). Be sure to specify a primary key, either simple or composite primary key, for each table. Be sure that the data type of a foreign key is the same as that of its corresponding primary key. Note, however, if the data type of a
Primary key is an AutoIncrement, the data type of its corresponding foreign key should be Long. You do not need to enter any sample data to your tables. A Simple Relational Schema for a Restaurant System.
OrderHeader
PK OrderID FK1
FK2
FK3 Date
Time
TableNo
CustomerID
StaffNo OrderLine
PK,FK1
PK,FK2 OrderID
Meal_ID PK Bill
PK BillNo FK1 Date
Time
BillAmount
OrderID Reservation FK1
FK2 Date
Time
PartyOf
TableNo
CustomerID Customer
PK Meal_ID
MenuID PK MenuID FK1 Season
MenuTypeID MenuType RecipeDirection Size
IsReserved ReservationNo Menu Name
Description
UnitPrice
Size Quantity
UnitPrice TableNo PK PK,FK1
PK,FK2 Meal_ID Table
PK MealMenu Meal CashReceipt
PK ReceiptNo FK1
FK2 Date
Time
PaymentType
TotalAmount
BillNo
CC_No PK PK CC_No FK1 FirstName
LastName
ExpiryDate
VerificationCode
CustomerID FK1 ServingSize
Directions
Meal_ID PK MenuTypeID
FromTime
ToTime StaffNo
FirstName
LastName
Street
City
State
Zip
Phone
Gender
BirthDate
JobTitle
Salary
JoinedDate StaffMeal
PK,FK1
PK,FK2 PK,FK1 StaffNo
FullName
Relationship
Street
City
State
Zip
Phone Meal_ID
StaffNo Recipe
PK,FK1
PK,FK2 RecipeID
ItemNo
Quantity Ingredient Manager
PK,FK1 NextOfKin
CreditCard RecipeID Staff CustomerID
Name
Phone
Email PK StaffNo PK,FK1 ItemNo
UnitOfMeasure
CaloriesPerUnit Performance
PositionDate
CarAllowance
MonthlyBonus SupplyItem
PK ItemNo
Name
Description
QtyOnhand
SupplyType