1
MIS303 – Access Assignment I (50 pts)
Fall 2020
In this assignment, students need to create a 3-table database for a textbook publishing company.
This is an individual assignment. Each student should work by himself or herself. If you have any question, you should contact the instructor for
help. Any type of collaboration, help, or consultation between students or from outside of our class is not allowed. Violated parties will receive
penalties, including receiving 0 point for this assignment, receiving F in this course, or being reported to the university academic integrity office,
etc.
Creating the database with three tables and two relationships
ABC Publish Inc. is a textbook publishing company. You are recently hired by the company to build a database to support their data management.
The major business operations that your database should support include author management and book version management. Three entities
have been identified from the business operations. They are Authors, Authorship Log, and Textbooks. Create a database using Access 2016, and
name it ABC Publish -First name Last name. Please note that you should include your own first name and last name in the database file name.
Data Model / Entity Relationship Diagram
Here is the entity relationship diagram that shows the data model of this database. It describes how the three entities related. Your database
should be created following this data model.
2
Field Requirements
Here are more field information for each entity/table, including data type and other rules you should apply on the fields.
Table: Authors
Fields Data Type Other Rules
A_ID Short Text Holds 4-character ID code that starts with a letter and followed by 3-digit number (e.g., A003);
Primary key
First_ Name Short Text No more than 25 characters long
Last_ Name Short Text No more than 25 characters long
Middle_ Name Short Text No more than 25 characters long
Email Short Text Required
Phone Short Text Shows in the form like (xxx) xxx-xxxx
Mailing_ Address Short Text
City Short Text
State Short Text
Zip Short Text Shows with 5-digit zip code like xxxxx
SSN Short Text Shows in the form like xxx-xx-xxxx
Table: Textbooks
Fields Data Type Other Rules
ISBN Short Text Holds 13-digit number; Primary key
Subject Short Text
Title Short Text
Edition Number Integer number, e.g., 1st edition should have value 1, 2nd edition is 2, etc.
Pub_ Year Short Text 4-digit year number
Versions Short Text Holds a 4-character code.
Print_ QTY Number Long integer number; this field stores values for total copies printed
List_ Price Currency Holds a number with two decimal places, with $ sign
Unit_ Cost Currency Holds a number with two decimal places, with $ sign
3
Note: For the next table, you will import the authorship log data from a spreadsheet. But during or after the import process, you need to
make sure your field names, data types and other rules meet what described here.
Table: Authorship Log
Fields Data Type Other Rules
Log_ No Short Text Primary key
ISBN Short Text
A_ID Short Text
Auth_ Order Number Integer number. E.g., First author should have value 1, 2nd
author is 2, etc.
Compensation_ PCT Number Double number to hold the percentage rate that an author
can get from the book sales profit.
4
Data Records
Here are the records you should enter into the Authors and Textbooks tables. You should have imported data from Authorship.xlsx (find and
download this file from the Blackboard assignment link) to make the Authorship Log table.
Table: Textbooks
Table: Authors
Your Tasks in this Assignment
1. Create a new database, and name it ABC Publish-First name Last name. Save it onto your computer.
2. In this database, create two tables (Authors and Textbooks) from scratch, using the field information and data records provided above.
3. Import the third table – Authorship Log from the spreadsheet file provided.
4. Create a relationship board with all three tables and two relationships. Your completed relationship board should look similar like the
data model on Page 1 of this instruction document (symbols would be slightly different though).
Submission:
Make sure you close the database file on your computer before you upload it to the Blackboard submission link. Otherwise, you may upload an
incorrect or un-openable file.
