Reference no: EM132333629

Database Management Systems Assignment -

BRIEF: The purpose of this assessment is to assess the student's skills in interpreting a given specification for modelling and developing a database. The specification is given in a form of a case study which best reflects a real world application or database environment. The real world application for this case study is based on Newegg, a large retail store selling electronic devices and appliances, computers, mobile phones, tablets, TVs, cameras, and so on. Find more information from the company's website.

It can be assumed the company and its online retail store support the following sub-systems:

• Inventory control - products added to and sold through the system
• Customers - new and existing customers can edit personal details
• Sales - orders, payments, refunds, deliveries, invoicing, insurance
• IT services and training - clients, sessions, open learning centres
• Human resources - staff, training, pay, job applications, leave, and etc.

You are required to browse to Newegg website and analyse the company's functionality in the context of a database driven application, not as a website. The database should be considered from an enterprise level with their online store in detail in addition to their 15 physical stores across New Zealand that makes use of the database. Enterprise level databases tend to have a large schema with around 30+ entities, a number of views and stored procedures or functions. It is expected of you to produce a database of similar magnitude.

The four parts of this assessment cover aspects of database development from planning to the physical implementation. Although this is a traditional SDLC approach, your lecturer may allow some modular and cyclical (Agile) development.

All of your SQL scripts should work on either a MS-SQL server, PostgreSQL or MySQL server. These servers will be available in the Software Lab.

1. THE PROPOSAL DOCUMENTATION

The proposal is the document describing the problem area, addressing or identifying potential issues and summarising the development process. The proposal should also show a development plan and describe the development methodology that will be adopted.

Requirements:

Executive Summary including and not limited to the following areas

• Description of the problem to be addressed.
• Identification of any issues arising out of the database design specification.
• A description of the development methodology that is being adopted.

Plan of the development process as applied to the SDLC (Hoffer et. al. 2016) - (Show dates and activities).

Enterprise data model.

Proposal should be approximately 3-4 typed A4 pages.

The database in this case study is a 3 tier model with emphasis placed on the enterprise/database tier as reflected in Chapter 1 of the textbook.

Models and methods must be specific and applicable to this case study, and are not to be generic.

2. PLANNING AND LOGICAL DESIGN

This part of the assessment considers the logical structure of the database. Here the existing system is to be analysed to determine how it works and identify the type of information to be stored in the database.

Requirements:

Analyse and record at least 10 potential business rules that will be included in the design of an entity-relationship model.

Develop a fully attributed Entity-Relation diagram (include attributes without data-types).

Examine your ERD and identify 2 areas that could be potential referential integrity issues.

Produce sample reports for the following:

• Customer list with addresses.
• Product list for given category (actual products from Newegg) - Each student is required to use a different brand for the data.
• Total sales for over a given period (start date to an end date)
• Order or Invoice for a given customer.

Indicate on each report the attributes used to provide the necessary information.

3. PHYSICAL DESIGN AND IMPLEMENTATION

This is the major activity associated with the database development. It should include the documented queries and testing plans.

Requirements:

With the aid of one of the database servers and the database administration tool you are to design/create and produce the following:

Comprehensive data dictionary (including all data types)

Develop SQL queries to create all the tables with appropriate constraints and indices.

Develop SQL views to produce the following information:

• Customer list with addresses per store.
• Comprehensive Product list for given category (actual products from Newegg). Each student is required to use a different brand for their sample data.
• Total Monthly sales for a given year per store.
• Order or Invoice for a given customer.

Develop 2 stored procedures or functions (depending on the server) to;

• apply a price increase for all the products in a selected category and;
• produce all the necessary data for a single invoice that is ready to be printed,

Discuss how you would implement data and user permissions. Create a matrix to support your discussion.

Develop a basic MS-Access application that has at least 4 data entry forms that use the above views, tables and stored procedures.

Develop MS Excel spreadsheet that connects to your database and uses the Daily sales per store view over a given period.

4. PRESENTATION/DEMO

Requirements: Clear/understandable demonstration of the working database; Be ready to demonstrate any time after the due date.

Demonstration includes but not limited to:

• The queries to provide the data for two of the sample reports from PLANNING & LOGICAL DESIGN;
• Two cases of referential integrity issues;
• Two general queries (SELECT) requested by the lecturer (looking for on demand information) and;
• Working database with all of the tables and sample data
• Demonstrate two of the data entry forms.
• Clear and error free demonstration.

RESOURCES -

• Use Visio, Dia, or Lucid Chart for your database diagrams (other tools not recommended).
• A MS-SQL server and a PostgreSQL server are available in the lab.
• Database administration and modelling tools will be made available on the shared network drive.
• There are a number of SQL tutorials and references on EIT online.

REFERENCES - Hoffer, J., Ramesh, V. & Topi, H. (2016). Modern Database Management, (11th ed.) Prentice Hall.