COMP 122ASSIGNMENT 4

NOTES & GUIDELINES

COMPLETION DATE

A Database exists as an important part of an information system. As you may recall, information systems also contain people, procedures, input data, output data software and hardware. Your database will be an essential component of an e-commerce solution. Its goal is to facilitate the production of a system that supports the business needs, business requirements, is easy to use, and meets users’ needs. Moreover, it must store all the relevant data and provide information in proper formats for customers, employees and vendors.

The first step in the database design process is to determine the data requirements which involve:

1. Collecting and evaluating existing data

2. Research missing or incomplete data

3. Talk to users to determine operation of the system, their needs, and the desired outputs

You will use Oracle 12c on cencol to create and maintain the database.

You will follow the traditional systems development life cycle for this project. Hence, your database design will encompass the activities associated with requirements engineering which consists of requirements elicitation and requirements specification.

1. DATA REQUIREMENTS PHASE

The data requirements phase is concerned primarily with fact finding and would involve such things as interviews, surveys, research, etc which are done during requirement elicitation

2. CONCEPTUAL DATA MODELING PHASE

Schema: The schema contains descriptive information about the data stored in the physical data store and includes:

• Organization of individual stored data items in the tables

• Relationships / Associations among tables as shown in Entity Relationship diagrams (ERDs)

• Details of physical data store organization including field name, field types, field size and other related metadata.

• Access and content controls, allowable values used for specific data items, list of users allowed to read or update data items.

3. LOGICAL DATABASE DESIGN PHASE

The term logical design is used to refer to the tasks of creating a conceptual data model that could be implemented on any DBMS. Here you will transform the conceptual data model into a format that can be understood by a commercial DBMS. The logical design, for a database must specify the appropriate conceptual data model, inputs, processes, and expected output requirements. Once a specific DBMS has been selected, the internal model maps the conceptual model to the DBMS. Hence, the internal model is a representation of the database as “seen” by the DBMS. This, therefore, requires the designer to match the conceptual model’s characteristics and constraints to those of the selected implementation model. The internal schema depicts a specific representation of an internal model, using the database constructs supported by the chosen DBMS.

4. PHYSICAL DATABASE DESIGN PHASE

This phase is concerned with the performance of the database at one computer location only. For a distributed database, physical design considerations are necessary for every location.

This phase yields the following:

a) The internal schema which represents the storage view of the database. It defines files, indexes and data placement.

b) A populated database that is functional.

Assignment 4Deliverables

Conceptual modeling requirements:

For all the entities you wish to store data about you must create ERDs to show the binary relationships between related entities, and then combine these ERDs to give the global view of the database system. All ERDs must be drawn by a modeling tool such as Visio and upload to the dropbox, a pdf is also permitted.

Database and tables requirements:

1. Create the e-commerce database on oracle1.

Each table must include a primary key and all necessary fields. You must set the field properties as necessary to collect the current data. You must do a table design for each table showing a descriptive column name, data type, size, and constraints where necessary.

2. Use the prefix comm_ for each of your table names.

3. All products must belong to a category and products can have attributes such as colour or size.

4. Products must include prices.

5. The shopping cart must indicate products, prices and quantities and a status, i.e., checked out or not.

6. Customers must have relevant contact information.

7. Shipping may be to the home address or any other address. There must be a shipping status with a discreet set of values.

8. Populate each table with at least 5 records.

9. Upload your database script file .sql to the Assignment 4 dropbox.

Note: Any form of plagiarism will be severely dealt with, and no mark will be awarded for the project. Please refer to the College policy outlined in the student’s rights and responsibilities provided to you by the college.