ACCT-2013 Accounting Information Systems
Assignment 2 (20%)
Due – 31 July 2017 (Week 13)
This assignment consists of two parts (02) and seven (06) questions. Answer all the questions.
Part 1 (12%)
At GBC College, a modernization effort is underway, which includes building integrated database systems and applications to perform basic business functions.
The following information shows the desired output from the new system:
1 – Student Report
Student ID, Student Name, Student Address, Student Telephone
2 – Course Report
Course ID, Course Title, Number of Credits, List of Prerequisite course(s)
3 – Instructor Report:
Instructor ID, Instructor Name, Instructor Address, Instructor Telephone, List of Approved Courses
4 – Section Report
Section ID, Course ID, Year, Semester, Building #, Room#, Day, Start Time, End Time, Instructor
|Student ID||Student Name||Grade|
- Only one mailing address and one phone number are kept for each student
- Each course may have one or more prerequisite courses
- Only one mailing address and one phone number are kept for each instructor
- Each instructor is qualified to teach multiple courses as indicated in report #3
- Any course may be offered multiple times, even in the same year and semester. Each offering is called a “Section”.
- Multiple students are registered in each section
Part 1 – Question1 (6%):
Apply the normalization process and normalize each view to relations (table) that are in third normal form. Consolidate the normalized relations you develop as you go. Clearly indicate the primary key, foreign keys, and other attributes for each relation.
Part 2 – Question1 (6%):
Draw a logical data model (E-R Diagrams) with one entity (rectangle) for each of your normalized relations and appropriate relationship lines with cardinality clearly noted.
Hint – Draw a line from each foreign key to the primary key and mark the foreign key and the primary kay end of the line with appropriate symbols.
Part 2 (8%)
Joe’s is a small ice-cream shop located near the local university’s baseball field. Joe’s serves walk-in customers only. The shop carries 26 flavors of ice cream. Customers can buy cones, sundaes, or shakes. When a customer pays for an individual purchase, a sales transaction usually includes just one item. When a customer pays for a family or group purchase, however, a single sales transaction includes many different items. All sales must be paid for at the time the ice cream is served. Joe’s maintains several banking accounts but deposits all sales receipts into its main checking account.
Joe, the owner of the ice-cream shop, purchases ice cream from two vendors. Over the years, he has developed good relationships with both vendors so that they allow Joe to pay them biweekly for all purchases made during the preceding two-week period. Joe calls in ice-cream orders on Mondays and Thursdays. The orders are delivered the next day. Joe buys ice-cream toppings from one of several local stores and pays for each such purchase at the time of sale with a check from the company’s main checking account.
Part 2 – Question 1 (2%)
Draw an REA diagram, complete with cardinalities, for the revenue cycle described above.
Part 2 – Question 2 (2%)
Draw an REA diagram, complete with cardinalities, for the expenditure cycle described above.
Part 2 – Question 3 (2%)
Draw an integrated REA diagram of both the revenue and expenditure cycles for the above information.
Part 2 – Question 4 (2%)
Develop a set of relational database tables to implement the integrated REA diagram you developed in question Part 2-Question 3. Present the information in the following format:
|Table Name||Primary Key||Foreign Keys
|At least one (01) other attribute.|