ITECH1006/5006 Database Management Systems

  • CRICOS Provider No. 00103D Assignment 1 Specification Page 1 of 4
    Faculty of Science
    Database Management Systems
    Assignment 1
    Development of an ER Diagram and Database Implementation
    Weight 20%
    To give you practical experience in using Entity-Relationship and Relational Database modelling
    Due Date:
    Monday of Week 7 (see Course Description for further dates and times).
    Project Specification
    The proprietors of BigM have approached you and asked if you could design a database to help them
    manage their business. The management has commissioned you (in your capacity as a Database
    Management System consultant) to analyse, design and develop an appropriate conceptual data model
    and relational database schema, based on the following information gathered about the current
    business activities.
    ? BigM operates stores in many cities in Australia. Stores are referenced by store number. BigM
    also keeps store name, phone, fax, email for each store. Each store has a postal address
    containing street address, city, state and postcode.
    ? Each store has a number of departments, for example, finance, accounts, sales, customer
    service etc. For each department, there is a department ID, department name, phone and e-mail
    ? Each department has a number of employees. For each employee, BigM keeps a record of their
    employee ID, first name, last name, phone, date of birth, start date of his/her work and tax file
    ? BigM also stores postal and residential addresses of their employees. For each address they
    need to store street address, city, state and postcode.
    ? An employee can be employed as casual or fulltime basis. For a casual staff, his or her job type
    (for example, accountant, sales, customer service etc.), and hourly rate is recorded. For a
    fulltime staff, his or her job type and annual salary is recorded.
    ? Each store is managed by an employee as a store manager and each department is supervised
    by an employee. The department supervisor is also the supervisor for all the staffs within that

  • images-19-1-24
    ? Each store may be assigned a supervising store where all training, payroll, server application
    and help desk are located.
    ? Each supervising store generates pay slips for all staffs (in this store and other stores being
    supervised) on a fortnightly basis. For each pay slip, the store records a pay ID, supervising
    store ID, employee ID, pay date, number of hours and the gross payment.
    ? BigM sales different products like CDs, cloths, computers etc. For each CD it keeps a record of
    CD number, title, length, number of tracks, release date, category description, price and reorder
    ? BigM would like to maintain information for each artist in the CD. They would like to include first
    name, last name and date of birth and website address, if they have one. If possible, BigM also
    wants to store number of tracks where a particular artist appears in a given CD.
    ? For cloths and other products it stores product number, product description, brand, product size
    and price.
    ? An inventory of the number of each particular product in each store is kept. BigM keeps track of
    CRICOS Provider No. 00103D Assignment 1 Specification Page 2 of 4
    the quantity of each product that is on order, as well as the number currently available in each
    ? Customer may place orders in the store. Customer details are always taken at each order. A
    customer is referenced by a customer number, customer first & last names, phone number and
    postal address, if available. For each address they need to store street address, city, state and
    ? A customer may order more than one product at a time, and they may order multiple copies of
    the same product. BigM also records the date a product arrives and the date when it is picked up
    by the customer. Note that these dates may be different for each product.
    ? BigM understands that they may not have provided you with sufficient information. If you need to
    make assumptions about their organisation please ensure that you record them.
    Assignment Submission
    You should produce a single pdf file, created using Word, containing all of the requirements as
    specified within this document. Your lecturer will provide details of how the file is to be submitted
    within lectures.
    The design document should contain:
    1. A completed copy of the SITE Assignment Coversheet.
    2. An appropriate title page that includes an acknowledgement of all students you have
    spoken to about the assignment.
    3. A table of contents and automatically generated page numbers.
    4. An entity relation (E-R) diagram that models the problem which includes:
    a. all entities, relationships (including names) and attributes;
    b. primary (underlined) and foreign (italic) keys identified;
    c. cardinality and participation (optional / mandatory) symbols; and
    d. assumptions you have made, e.g. how you arrived at the cardinality/participation
    for those not mentioned or clear in the business description, etc.
    The E-R should be completed using the standards of this course (crow’s feet).
    5. Normalisation of relations which identifies:
    a. dependency diagram for each relation
    b. the level of Normalisation achieved for each relation;
    c. the reasons for any relation that is maintained NOT in 3NF.
    6. Relational data structures that translate your E-R diagram which includes:
    d. relation (table) names,
    e. attribute (column ) names and field types (as required by WAMP),
    f. primary and foreign keys identified;
    The data structures should be shown using the standards of this course.
    7. A bibliography, in APA format, containing all resources used to complete the assignment.
    If no resources have been used please indicate this appropriately.
    Your assignment should be completed according to the University of Ballarat General Guide
    for the Presentation of Academic Work. This is available online at the following web address:
    Assessment Criteria
    1. How clear and well-presented your submission is.
    2. E-R diagram: adherence to our standard, assumptions made, inclusion of correct primary
    CRICOS Provider No. 00103D Assignment 1 Specification Page 3 of 4
    and foreign keys, appropriate entities, relationships, and attributes.
    3. Normalisation: appropriate interpretation of each normal form, arguments for leaving the
    schema in the normal form you consider optimal.
    4. Conversion of E-R diagram to relational schema: schema is a correct translation of the E-R
    submitted with appropriate tables, columns, and primary keys.