Faculty of Engineering & Built Environment School of Electrical Engineering and Computing INFO6001: Database Management 1 Assignment 1: Gourmet Pizzas Project – Requirement Analysis and Conceptual Design Due: 12 pm, Wednesday, March 1, 2017 WORTH 10% of final assessment mark. 1. Background Gourmet Pizzas is a pizza outlet at Charlestown. After years of managing the records manually, Gourmet Pizzas has decided to computerise its records. You are tasked to design a database for the shop. The proposed database system is developed in various modules, including requirements analysis, conceptual database design, logical database design, and physical database design. In this assignment 1, you are required to develop user requirements specification and the conceptual database model for Gourmet Pizzas's database based on the business requirements provided in this document. Your lecturer will act as your client and you can query him for any further information and clarifications. 2. Main Features and Business Requirements Order Processing Gourmet Pizzas takes orders via phone as well as through walk-in customers. Gourmet Pizzas provides both delivery and pickup services. When a customer orders via the phone, the customer's phone number is entered to the system along with the id of the person taking the order. If the customer has previously ordered by phone, the name and address appear on the screen. The customer is then asked for his name and address and then the order is taken. If the customer has not ordered before or if the name and address given do not correspond with that in the computer, a new customer record is created and the order is taken. After the order has been taken, a verification process occurs whereby the assistant dials the number given and confirms the order with the customer. If it is not confirmed, the customer is recorded as a hoax and order is kept on hold (if and until the customer calls back in which case the verification process takes place before hoax is removed and order goes through). The time the call was answered as well as time the call was terminated is recorded for each phone order. For a walk-in customer, the customer's name is recorded to identify the order. Each order contains date of order, one or more items, quantity of each item, price of each item, subtotal, discount amount (if any), discount code (if any), tax, total amount Page 2 of 4 due, payment method, status, type (phone/walk-in) and description. If the payment is via card, a payment approval number is recorded. If the order is a delivery order, the driver who delivered the order is recorded. There are different types of discount programs offered by Gourmet Pizzas. All discount programs are recorded in the database. A discount program has a discount code, start date of discount program, end date of discount program, requirements for discount, discount percentage (e.g. 5%) and a description. Menu Items, Ingredients and Suppliers Each item in the menu has an item code (unique), name, size and a current selling price. An item in the menu is made up of a number of ingredients. The ingredients and quantities used for the item are recorded in the database. Each ingredient has a code (unique), name, type, description, stock level at current stocktake period, date last stocktake was taken, suggested current stock level, reorder level and a list of suppliers who supply the ingredients. A supplier can supply many ingredients. Each ingredient can be supplied by many suppliers. A stocktake is taken each week, where the actual levels of ingredients in store are recorded. This is then compared with suggested levels (based on orders for the week). This report is used by the store manager to order ingredients for the following week. Information about ingredient orders needs to be maintained in the database. Employees Employees at the store can be divided into two types: those who work in the shop are paid hourly and those who carry out deliveries are paid by the number of deliveries. For each employee, there is an employee number, firstname, lastname, postal address, contact number, tax file number, bank details (bank code, bank name, account number), a payment rate, status, and a description. Drivers also have a driver's license number. Hours are not regular and a record is kept for each time an employee works – a shift (start date, start time, end date, end time). The orders a driver delivers during a shift is kept on the record. Payment rates for shop workers and drivers are maintained in the database. Employee payments are made for each shift to the employee's bank account. Employee payment records needs to be maintained in the database. 3. Assignment (individual assignment) The proposed database system is developed in various modules, including requirements analysis, conceptual database design, logical database design, and physical database design. In this assignment 1, you are required to complete the first two stages of the database design, i.e., to develop user requirements specification and the conceptual database model for the database based on the business requirements provided in this document. There are two parts to be completed in assignment 1 as described below. Page 3 of 4 Part 1: Requirements In this assignment, you are required to develop a user requirements specification truly fulfilling the data requirements (identify what types of data needs to be stored in the database), transaction requirements (identify the important and frequent database operations – data manipulation and queries), and business rules for the Gourmet Pizzas's database mentioned above. Assignment submission format for the Requirements part: The requirements document MUST have the following sections: • Data Requirements – outlining the major data items • Transaction requirements – outlining the data manipulation and queries • Business Rules Hint: Sample requirements documents are discussed in weeks 2 and 3, and are available in appendices A and B of your main textbook. You may interview your client (i.e. lecturer) for clarification and include your interview questions and responses Part 2: EER Model with data dictionary Draw an EER model for the requirements identified in Part 1. The EER Model must be shown in UML notation which is discussed in class and provided in our text. The EER Model should be accompanied with a data dictionary which includes entity type table, relationship type table and attribute table. Assignment submission format for the EER Model with Data Dictionary part: The requirements document MUST have the following sections: • EER Model • Documentation – Data dictionary details (description of entities, relationships and attributes) Sample format for documenting the data dictionary is provided below. Data Dictionary Format: Use the format described in your main text in documenting the data dictionary. Following provides samples for reference. ENTITY TYPES Entity Name Description Aliases Occurrence Collection A collection is a physical collection of items in the library located at a particular physical location Physical area of the library is divided into a set of collections ... RELATIONSHIP TYPES Entity name Multiplicity Relationship Multiplicity Entity name PhysicalCopy 0..* LocatedIn 1..1 Collection ... Page 4 of 4 ATTRIBUTES Entity Name Attributes Description Data Type & Length Nulls Multi- valued Derived Default Student studentId A unique id given to every student char N N N Hint: Sample EER models and documentation is provided in Chapter 16 of your text. The lecturer will discuss the details of the requirements in class as well as act as the client of the system. You need to implement all the details mentioned in lecture as well as described in this document. You are encouraged to ask questions to the lecturer to clarify requirements. Method of submission: Both softcopy submission and hardcopy submission are required: • zip all required files into one zip file. The file name MUST be identified by 4 sections: A1, your first name, your surname, and your student number, e.g., A1SimonLee1234567.zip • It must be submitted to Blackboard -> Assessment -> AssignmentsSubmission-> Assignment1 • Print the document, hand in the hardcopy to the lecturer by the due time. The hard copy must have on the front a signed copy of the cover sheet (Assessment Item Cover Sheet – Individual) which is available from: http://www.newcastle.edu.au/__data/assets/pdf_file/0008/75383/AssessmentIt emCoverSheet.pdf Note: Ten percent of the possible maximum mark for the assessment item will be deducted for each day or part day that the item is late. Weekends count as one day in determining the penalty. Assessment items submitted more than five days after the due date will be awarded zero marks. Please note: if your hardcopy submission and softcopy submission are not at the same time, Sample Requirements Document Library Project Description School of DCIT University of Newcastle Callaghan, Australia Data Requirements 1. Catalogue The library maintains information about its physical and online information sources. Library has many types of physical resources that it maintains which include: books, journals, videorecording, sound recordings, microform materials and theses of the university. Collections There are many collections in each library. A collection consists of unique name – "Auchmuty – Serials", etc. and specifies the location where the collection exists physically in the library. A physical item in the library belongs to a collection. Books Information on books maintained by the library include authors (principle author and other authors if any), title of the book, publisher information, edition, physical description (which include number of pages, size, etc.), notes of the book (such as previous editions, includes bibliography, etc.), contents of the book, a summary of the book (if available), ISBN/ISSN numbers (there could be many ISSN/ISBN numbers - for instance for US edition, Int'l edition, etc.) and the subject areas to which the book belongs. Videorecording Information on videorecording items (DVDs, videos, etc.) includes title of the videorecording, publisher information, physical description, notes, credits (which includes information such as director, producer), cast of the videorecording, summary of the videorecording, equipment (e.g. DVD, VHS), identification number, subjects to which the videorecording is classified, other authors and other titles that exists for the videorecording. Sound Recordings Information on sound recordings (CDs, cassettes, etc.) include authors (principle author and other authors if any), title of the sound recording, publisher information, physical description, notes, contents, summary, equipment (e.g. Cassette, CD), identification number and subjects to which the sound recording is classified. Theses Page 2 of 5 A copy of each thesis published by the university is maintained in the library. Information of theses maintained by the library includes author, title, published, physical description, notes, subject areas which the thesis belongs to, and other authors (usually the school which the student majored in). Journals Information on journals include title of the journal, publisher information, the volumes that the library have, physical description (size etc.), frequency of the journal publication (monthly, quarterly, yearly etc.), notes of the journal, ISBN/ISSN numbers, subject areas to which the journal belongs, other organisations that are responsible for authorship (if any), any abbreviated titles (where applicable), other titles for the journal (if any), later and previous titles (if any), and mainseries to which the journal belongs (if any). Microform Resources such as newspapers are maintained by the library on microfilm. Information on microform materials includes title of microfilm, publish information, physical description, notes, subjects to which the microfilm belongs, other titles by which the microfilm material is known and prior titles used (if any). Item Each item of a particular type (book, videorecording, sound recording, thesis, journal and microfilm) in the library has a unique call number assigned to it based on the primary subject the item belongs to. Copy of Item All copies of items have a unique barcode. A copy of item (except for journals and microform) has a copy number (e.g. c1, c2, ...). The call number of the item type and copy number together is unique for each copy of an item. For copies of journals and microform, an issue number and dates of the issue is assigned. Each copy of item has a location (collection to which it belongs) and status ("in library", "on loan", etc.). Online Information Sources The library maintains access information for each online information source that it subscribes. There are three main types of online resources to which the library subscribes: EBooks, EJournals and online databases. EBooks and EJournals In addition to the physical book information, ebook information contains a list of access information for the different online subscriptions providing the ebook. The access information includes a URL of the resource, a username and password used to connect to the resource. Page 3 of 5 Similarly, ejournals subscriptions maintain journal information and a list of access information to access the different journal subscriptions. Online Databases Information of online databases maintained by the library include author, title of the database, publish information, subjects to which the online database belongs, other titles (if any), notes and access information which includes URL, username and password. 2. Circulation The library facilitates information access to patrons/members of the library. There are different types of members with varying privileges. Fines may be imposed on members who do not adhere to library rules and loan privileges may be withdrawn. Members Every member has a member number, PIN, name, date of birth, home address, home phone, email, mobile, date joined, expiration date, member type to which he/she belongs and status of membership. Member Types There are different types of member. Each member type has a unique type number, name and a description. Each member belongs to a member type. Each member type has different information access privileges. Privileges Privileges are granted to different member types. A privilege contains a unique id, name of privilege, description, collection to which the privilege is granted, loan period and maximum number of items of the type accessible for the loan period Loans Library maintains a record of each loaned item. Information such as item loaned, member loaning the item, date and time loaned, date and time returned, date and time item is due and number of times it is renewed. A member can renew a loan once if there are no holds/reservations on the item. Fines Library maintains information about fines due to the library. Fine information include description of fine, fine amount, member from whom the fine is due and if overdue fine, which loan the resulting in the fine. Different types of fine amounts to different member groups are applied. Data on fine rates and policies are not maintained in the database. Page 4 of 5 4. Hold Requests Hold on items can be placed on items borrowed (i.e. not available on shelf) by member. A hold request contains a call number of the item on hold, member requesting the hold, date and time the request is made, status of request and comments. On their return they will be put on the "hold shelf" to await collection and hold requestor is emailed of the available item. If an item on hold is not collected within 5 days, it will be returned to the Library shelves or the next hold will be activated. Next hold is determined on the date and time the hold request is made. A member can have up to 10 holds at any one time. Transaction Requirements (Sample) Data Manipulation Operations * Insert/update/delete an item in the library * Insert/update/delete a copy of item in the library * Insert/update/delete an online information source * Insert/update/delete members * Insert/update/delete member types * Insert/update/delete privileges * Insert/update loan information * Insert/update/delete fines * Insert/update/delete acquisition requests * Insert/update/delete hold requests * Insert/update/delete short loan reservation Queries * Search an item based on call number, keyword, author, title, subject and journal title (keyword) * List present loans by a particular member * List frequently loaned items for each semester * Fines report containing information about fines levied and members fined * Report of items overdue and members who have overdue items * List acquisition requests for the last week * List acquisition requests that are satisfied during last month * List current hold requests by a particular member * List short loan reservations made by a particular member