COMM 226 Business Technology Management Department of Supply Chain and Business Technology Management Assignment 2 - Part A Winter 2016 - Section Y You will complete this assignment in teams of 2 members (no more, no less). Due Date: 11:55 pm, March 17, 2016, in Moodle This part of the assignment is worth 7 marks. Using the business case (next page), you need to complete the following requirements: 1. Using Visio or any other drawing software, draw the ERD (attributes, entities, relationships and cardinalities (e.g. one-to-one, one-to-many) of the database used to support the operations of the business described in the case. The ERD should identify all primary keys and foreign keys. If you decide to use Visio (2013), you can watch the video tutorial below that shows how to draw an ERD with this tool: https://www.youtube.com/watch?v=K0mNzXnHkYc&feature=youtu.be The PowerPoint for the video tutorial is available from the link below (Please notice that the tutorial for the creation of data dictionaries in only included in the video for Visio 2010): http://jmsb.concordia.ca/~rvalverde/Assignment2tutorial.pptx Please bear in mind that Visio only works on Windows. If you have a Mac, you might want to use the DIA diagram editor available from the link below: http://dia-installer.de/download/macosx.html A video that shows how to use this software to draw ERDs is available from the link below: https://www.youtube.com/watch?v=t56VSRrGLwI&feature=youtu.be 2. Create the data dictionary for your database. To do this, you can use the template that can be accessed from the link below: http://jmsb.concordia.ca/~rvalverde/datadictionaryTemplate.xls You must hand in a single WORD file that includes the following: ERD diagram embedded (e.g. Visio Diagrams) Data dictionary created in an Excel file embedded Note: ERD diagrams in native formats will not be accepted Important note: The first page of your word document must clearly identify team members' last name, first name, and student IDs. Handwritten assignments will not be accepted. Business Case: The Montreal Art Museum owns numerous collections of works of art. Some collections are small but most collections include a large volume of unique works of art. Collections are differentiated by their type (e.g., sculptures, Italian paintings, French paintings, drawings, photographs). The Museum also keeps track of the number of pieces each collection comprises. Five curators work at The Art Museum each of them being responsible for a number of collections depending on their sizes. The Art Museum has to keep relevant information about their curators such as their name, address, home phone number, cell phone number, social security number, hiring date, and salary. Each collection has at least one curator; some larger collections have two curators. Each work of art has an item code (identifier), a title, type, and size (height and width). For the sculpture collection, the weight is also an important characteristic. At any point in time, a work of art can either be on display at the museum, held in storage, or away from the museum as part of a traveling show. Each work of art is created by an artist but the artist for some works is unfortunately unknown. The Art Museum keeps as much information as it can about each artist such as his/her name, date of birth, date of death (which is null for still living artists), citizenship, and the total amount of pieces created or known to exist. The Art Museum only keeps information about artists for works currently owned by the museum. When a piece is rented to a traveling show, The Art Museum keeps information about the name of the show, the city in which the show is appearing, the start and end dates of the show, and the name and phone number of the contact person. Most of the times, traveling shows rent many pieces. Table Name CUSTOMER Atribute Name cust_id cust_lname cust_fname cust_lbilldate cust_balance cust_address cust_email cust_status cust_surveydate chan_num CHANNEL chan_num chantype_id CHANNELTYPE chantype_id chantype_name PACKAGE pkg_id pkg_name pkg_cost SUBSCRIPTION cust_id pkg_id PACKAGE_CHANNE chan_num pkg_id PROGRAM pgm_id pgm_name pgm_desc pgm_lentgh chantype_id rtg_code supp_id RATING rtg_code rtg_desc SUPPLIER supp_id supp_name supp_address supp_contact supp_phone SCHEDULE chan_num sch_date pgm_id AGENT agent_id agent_name ACCOUNT_MOD mod_id mod_date mod_type mod_notes agent_id cust_id pkg_id Contents Customer account number Customer last name Customer first name Customer last billing date Customer account balance Customer mailing address Customer email address Customer service status Customer survey submission date Favorite channel Channel Number Channel Type ID Channel Type ID Channel Type Name Package ID Package name Package cost Customer account number Package ID Channel number Package ID Program ID Program Name Program Description Program time length in minutes Channel Type ID Rating Code Supplier ID Rating Code Rating Description Supplier ID Supplier Name Supplier mailing address Supplier Contact Name Supplier Phone Number Channel Number Scheduled program date Program ID User ID Username Modification ID Modification date Modification type Account modification notes User ID Customer account number Package ID Type INTEGER VARCHAR2(25) VARCHAR2(25) DATE NUMBER(4,2) VARCHAR2(50) VARCHAR2(50) VARCHAR2(8) DATE INTEGER INTEGER INTEGER INTEGER VARCHAR2(50) INTEGER VARCHAR2(25) NUMBER(4,2) INTEGER INTEGER INTEGER INTEGER INTEGER VARCHAR2(25) VARCHAR2(50) INTEGER INTEGER INTEGER INTEGER VARCHAR2(2) VARCHAR2(50) INTEGER VARCHAR2(50) VARCHAR2(50) VARCHAR2(50) INTEGER INTEGER DATE INTEGER INTEGER VARCHAR2(50) INTEGER DATE VARCHAR2(6) VARCHAR2(50) INTEGER INTEGER INTEGER Format Range 999999999 xxxxxxxxxx xxxxxxxxxx yyyy-mm-dd 9999.99 xxxxxxxxxx xxxxxxxxxx xxxxxx 'active' or 'inactive' or 'deleted' yyyy-mm-dd 99 99 99 99 xxxxxxxxxx 99 xxxxxxxxxx 9999.99 999999999 99 99 99 99 xxxxxxxxxx xxxxxxxxxx hh:mm 99 99 99 xx xxxxxxxxxx 99 xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx 9999999999 99 yyyy-mm-dd hh:mm 99 9999 xxxxxxxxxx 9999 yyyy-mm-dd xxxxxx 'NEW' or 'ADD' or 'SUBSTR' or 'DELETE' xxxxxxxxxx 9999 999999999 99 Req'd PK or FK Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y FK Referenced table PK FK PK FK PK CHANNEL CHANNELTYPE PK PK,FK PK,FK PK,FK PK,FK PK CUSTOMER PACKAGE CHANNEL PACKAGE FK FK FK PK CHANNELTYPE RATING SUPPLIER PK PK,FK PK FK PK CHANNEL PROGRAM PK FK FK FK USER CUSTOMER PACKAGE Table Name CUSTOMER Atribute Name cust_id cust_lname cust_fname cust_lbilldate cust_balance cust_address cust_email cust_status cust_surveydate chan_num CHANNEL chan_num chantype_id CHANNELTYPE chantype_id chantype_name PACKAGE pkg_id pkg_name pkg_cost SUBSCRIPTION cust_id pkg_id PACKAGE_CHANNE chan_num pkg_id PROGRAM pgm_id pgm_name pgm_desc pgm_lentgh chantype_id rtg_code supp_id RATING rtg_code rtg_desc SUPPLIER supp_id supp_name supp_address supp_contact supp_phone SCHEDULE chan_num sch_date pgm_id AGENT agent_id agent_name ACCOUNT_MOD mod_id mod_date mod_type mod_notes agent_id cust_id pkg_id Contents Customer account number Customer last name Customer first name Customer last billing date Customer account balance Customer mailing address Customer email address Customer service status Customer survey submission date Favorite channel Channel Number Channel Type ID Channel Type ID Channel Type Name Package ID Package name Package cost Customer account number Package ID Channel number Package ID Program ID Program Name Program Description Program time length in minutes Channel Type ID Rating Code Supplier ID Rating Code Rating Description Supplier ID Supplier Name Supplier mailing address Supplier Contact Name Supplier Phone Number Channel Number Scheduled program date Program ID User ID Username Modification ID Modification date Modification type Account modification notes User ID Customer account number Package ID Type AutoNumber Short Text(25) Short Text(25) Date/Time Currency Short Text(50) Short Text(50) Short Text(8) Date/Time Number(Byte) Number(Byte) Number(Byte) Number(Byte) Short Text(50) Number(Byte) Short Text(25) Currency AutoNumber Number(Byte) Number(Byte) Number(Byte) Number(Byte) Short Text(25) Short Text(50) Number(Integer) Number(Byte) Number(Byte) Number(Byte) Short Text(2) Short Text(50) AutoNumber Short Text(50) Short Text(50) Short Text(50) Short Text(10) Number(Byte) Date/Time Number(Byte) AutoNumber Short Text(50) Number(Integer) Date/Time Short Text(6) Short Text(50) Number(Integer) Number(Long Integer) Number(Byte) Format Range 999999999 xxxxxxxxxx xxxxxxxxxx yyyy-mm-dd 9999.99 xxxxxxxxxx xxxxxxxxxx xxxxxx 'active' or 'inactive' or 'deleted' yyyy-mm-dd 99 99 99 99 xxxxxxxxxx 99 xxxxxxxxxx 9999.99 999999999 99 99 99 99 xxxxxxxxxx xxxxxxxxxx 999 99 99 99 xx xxxxxxxxxx 999999999 xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx 9999999999 99 yyyy-mm-dd hh:mm 99 999999999 xxxxxxxxxx 9999 yyyy-mm-dd xxxxxx 'NEW' or 'ADD' or 'SUBSTR' or 'DELETE' xxxxxxxxxx 9999 999999999 99 Required PK or FK Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y FK Referenced table PK FK PK FK PK CHANNEL CHANNELTYPE PK PK,FK PK,FK PK,FK PK,FK PK CUSTOMER PACKAGE CHANNEL PACKAGE FK FK FK PK CHANNELTYPE RATING SUPPLIER PK PK,FK PK FK PK CHANNEL PROGRAM PK FK FK FK USER CUSTOMER PACKAGE Atra Ahia-Tabibi 6440533 CATEGORY PK C_ID Elizabeth Montoya 6471013 BOOK has PK PK,FK1 BOOK_CATEGORY NUMBER_OF_COPY BOOK_BARCODE C_ID BOOK_ISBN BOOK_TITLE BOOK_AUTHOR BOOK_PUBLISHER BOOK_LOCATION BOOK_STATUS STAFF PK Student PK can be STUD_ID STUD_FNAME STUD_DOB STUD_LNAME BOOK_HOLDS STUD_STREET_NUMBER STUD_STREET_NAME STUD_CITY STUD_POSTAL_CODE STUD_PRVINCE STUD_COUNTRY STUD_TEL STUD_EMAIL operates LOAN BOOK can rent PK PK,FK1 PK,FK2 PK,FK3 PK,FK3 LOAN_ID STUD_ID STAFF_ID BOOK_BARCODE C_ID LOAN_DATE NUMBER_OF_BOOKS RETURN_DATE has RECEIPT PK PK,FK1 RECEIPT_ID LOAN_ID RECEIPT_LOCATION STAFF_ID STAFF_FNAME STAFF_LNAME STAFF_STREET NUMBER STAFF_STREET_NAME STAFF_CITY STAFF_POSTAL CODE STAFF_COUNTRY STAFF_TEL STAFF_EMAIL STAFF_PROVINCE STAFF_DOB