ABCPublish 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 o

AuthorshipLog

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

ABCPublish 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, AuthorshipLog, and Textbooks. Create a database using Access 2016, and name it ABCPublish-FirstnameLastname. 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: AuthorshipLog 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 AuthorshipLog table.

Table: Textbooks

Table: Authors

Your Tasks in this Assignment

1. Create a new database, and name it ABCPublish-FirstnameLastname. 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 – AuthorshipLog 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.