ICT503 Database Systems Assignment

Assessment Details for Assessment Item 1:

Objectives

This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to improve students’ skills to analyze organization database requirements, develop a data model to reflect the organization’s business rules. This assessment covers the following LOs.

1. Synthesize user requirements/inputs and analyse the matching data processing needs, demonstrating adaptability to changing circumstances.
2. Develop an enterprise data model that reflects the organization's fundamental business rules; refine the conceptual data model, including all entities, relationships, attributes, and business rules.
3. Derive a physical design from the logical design taking into account application, hardware, operating system, and data communications networks requirements; further use of data manipulation language to query, update, and manage a database

Case Study

This assignment concerns a liquor shop chain in Sydney, called the A-one liquor (AL). The objective of this assignment is to develop a database system that will be used to centrally store and manage all relevant information for the branches of AL.

The information to be stored include information on different branches of AL (e.g., Bankstown, Hornsby, etc.), types of drinks they sell (beers, wines, cedars, etc.), staff they employ (Retail Assistants, Shelving Assistants, etc.), AL Members (AL Loyalty Card holders). The basic requirements gathered from the stake holders is presented in the following four points. As typically the case, these requirements are often underspecified. Use your judgment in interpreting them when required and keep a note of the assumptions you made.

1. Branch Information: The AL System shall keep information on each branch including its name and address, and the number of employees who work there. The system shall also contain information on which days (Mon-Sun) the branch is open, and opening hours. It will also keep information on opening hours (e.g., Mon-Fri 10:00AM-5:30PM; Sat 9:00AM-9:00PM; Sun Closed).
2. Product Information: The system shall contain relevant information on products of different types at the “item level”, such as: (wine/beer/spirit/…), packaging info (can/bottle/…), volume (e.g., 375ml X 6 pack), price, and brand (e.g., Tooheys Old Dark Ale), as well as current stock level.
3. Staff Information: The system shall record information on staff members who work at different branches of AL. This will include their roles, type of employment (e.g., permanent, casual), salary (annual or hourly depending on permanent or casual), as well as who they report to (i.e., supervisors).
4. Membership Information: The system shall record information on AL members, including type of membership (Platinum/Gold/Silver), and when the membership will expire.

Assignment Requirements and Deliverables:

Part A - 20% (Due in Session 4)

Submitted as a MS Word Document

Task 1:                                                                                                                                                                                        (Weightage 20%)

Investigate and identify data requirement for the given study by providing list of required entities, list of attributes in each of these entities, and the unique identifier for each entity.

Task 2:                                             (Weightage 60%)

Develop an Entity-Relationships Diagram for the given scenario (It must contain entities and unique identifiers in terms of keys, relationships between the entities including relationships constraints, and the attributes).

Task 3:                                                                                                                                                                                        (Weightage 20%)

Derive the physical design having detail of entities, primary and foreign keys, detail of all the attributes.

Marking Criteria/Rubric

Assignment Requirements and Deliverables:

Part B - 20% (Due in Session 6)

Submit a single plain text file with filename as “studentid_studentname_AL_SQL” containing all SQL implementation. Your SQL queries must work on MS SQL Server and be able to be demonstrated. SQL code required (Use MS SQL Server):

1. Create a database and CREATE TABLE statements for all tables in your ERD (Part A) including primary and foreign keys.
2. INSERT INTO statements for populating the database
   1. Insert five rows of (made-up) data into each table. Make sure that the data you enter in these tables should be sufficient to return at least one row for each query in Task 3. AL should hold at least 5 bottles of Penfold Grange 2010 in some branch or other.
3. Select Statements
   1. List the branches (ID) of MA that have in stock at least 5 bottles of Penfold Grange 2010.
   2. SELECT statement to generate a list of all email addresses of members whose card will expire in the month after the coming month. Thus, for instance, if the query is run in November 2121, it will list the emails of all members whose membership will expire in January 2122. The emails should be ordered by Branch ID, then by expiry date, and then by the email address, all in ascending order.

Research and Discussion

Submitted as a MS Word or PDF Document

1. Consider the following relation schema as the join of a few tables from Assignment 1 ERD

Abnormal_Rel ( ProductID, BranchID, campaignID, MemberID, ProductType, PackageType, YearProduced, Price, Brand,

StockLevel, CampaignStartDate, CampaignEndDate, FirstName, LastName, eMail, MembershipLevel, MemberExpDate, Discount )

Determine for UPDATE anomaly whether or not the relation Abnormal_Rel is susceptible to that anomaly. Support your determination with adequate explanation and a small example.

1. Normalize/decompose the relation schema Abnormal_Rel until you get relations that are in 3NF. Use appropriate illustration to aid the understanding of your work

Marking Rubric/Criteria

Submission Instructions

All submissions are to be submitted through Turnitin. Drop-boxes linked to Turnitin will be set up in Moodle. Assessments not submitted through these drop- boxes will not be considered. Submissions must be made by the end of session 4 (Part A) and session 6 (Part B). The Exact dates will be communicated on LMS.

The Turnitin similarity score will be used to determine any plagiarism of your submitted assessment. Turnitin will check conference websites, Journal articles, online resources, and your peer’s submissions for plagiarism. You can see your Turnitin similarity score when you submit your assessments to the appropriate drop-box. If your similarity score is of concern, you can change your assessment and resubmit. However, re-submission is only allowed before the submission due date and time. You cannot make re-submissions after the due date and time have elapsed.

Note: All work is due by the due date and time. Late submissions will be penalized at 20% of the assessment final grade per day, including weekends.

Referencing guides

You must reference all the sources of information you have used in your assessments. Please use the IEEE referencing style when referencing in your assessments in this unit. Refer to the library’s referencing guides for more information.

• https://elearning.vit.edu.au/pluginfile.php/473840/block_html/content/VIT%20Library%20Refer encing%20-

%20IEEE%20-%2007042020.pdf

Academic misconduct

VIT enforces that the integrity of its students’ academic studies follows an acceptable level of excellence. VIT will adhere

to its VIT Policies, Procedures and Forms where it explains the importance of staff and student honesty in relation to academic work. It outlines the kinds of behaviours that are "academic misconduct", including plagiarism.

Late submissions

In cases where there are no accepted mitigating circumstances as determined through VIT Policies, Procedures and Forms, late submission of assessments will lead automatically to the imposition of a penalty. Penalties will be applied as soon as the deadline is reached.

Short extensions and special consideration

Special Consideration is a request for:

• Extensions of the due date for an assessment, other than an examination (e.g., assignmentextension).
  • Special Consideration (Special Consideration in relation to a Completed assessment, includingan end-of-unit Examination).

Students wishing to request Special Consideration in relation to an assessment the due date of whichhas not yet passed must engage in written emails to the teaching team to Request for Special Consideration as early as possible and prior to start time of the assessment due date, along with anyaccompanying documents, such as medical certificates.

For more information, visit VIT Policies, Procedures and Forms.

Inclusive and equitable assessment

Reasonable adjustment in assessment methods will be made to accommodate students with a documented disability or impairment. Contact the unit teaching team for more information.

Contract Cheating

Contract cheating usually involves the purchase of an assignment or piece of research from anotherparty. This may be facilitated by a fellow student, friend or purchased on a website. Other forms of contract cheating include paying another person to sit an exam in the student's place

Contract cheating warning:

• By paying someone else to complete your academic work, you don’t learn as much as youcould have if you did the work yourself.
  • You are not prepared for the demands of your future employment.
  • You could be found guilty of academic misconduct.
  • Many of for pay contract cheating companies recycle assignments despite guarantees of“original, plagiarism- free work” so similarity is easily detected by TurnitIn.
  • Penalties for academic misconduct include suspension and exclusion.
  • Students in some disciplines are required to disclose any findings of guilt for academicmisconduct before being accepted into certain professions (e.g., law).
  • You might disclose your personal and financial information in an unsafe way, leaving yourselfopen to many risks including possible identity theft.
  • You also leave yourself open to blackmail - if you pay someone else to do an assignment foryou, they know you have engaged in fraudulent behaviour and can always blackmail you.