Guidelines for Project Report 3: Database Design

This report represents the database design for the membership, billing, and related functions at Great Lakes Fitness Center. It is largely based on the enterprise data model in the holistic approach (Entity-Relationship Diagram in Report 1), and will also require you to ensure that this design is consistent with that compiled from the view integration approach (Report 2). The outcome of this process represents the database design for the system that you are working on. It will also serve as the basis for the individual projects SQL and Visual Basic. The report should

include the following:

Academic Integrity Statement

This must be attached for your project to be graded. See the course D2L page for details. It must be signed by all team members. Those choosing not to sign it will be deemed as not abiding by it and will earn no points for this project.

Data model

From Report 1, amended as needed. This is to be corrected so that gaps in the modeling (missing entities, attributes, relationships), as well as inaccuracies (spurious entities, incorrectly placed attributes, repeating groups, incorrect relationship cardinalities, and incorrectly identified keys) are rectified. All entities, attributes and relationships should be included. You may base the

se revisions on the solution I have provided for Report 1. If so, you must create the diagram anew, not simply attach my solution. Photocopies of my solution are not acceptable. If the diagram spans multiple pages, tape it together for readability.

View integration database schema

From Report 2, amended as needed.

This segment is to be rectified based on completeness and correctness of the final relations after the normalization and view integration exercises. The entire normalization exercise is not needed. It is expected that all relations will be in third normal form at this stage (with minor exceptions for addresses, etc.). Relations are to be presented in standard form, with foreign keys designated. You may base your revisions on the solution I have provided for Report 2. If so, you must create the relations anew, not simply attach my solution. Photocopies of my solution are not acceptable.

Logical database design

This forms the initial database schema for the reservation, charges, billing, and related functions at Great Lakes Fitness Center. It is assumed that the mapping is to a relational schema. For each table, you need to specify the columns that comprise the table, identify its keys, and specify any foreign keys, including columns that serve as both primary and foreign keys. The relations should be presented in standard form. You should ensure that common columns have identical names.

Resolution of differences

This segment comprises two parts an acknowledgement of the differences that exist between the two designs, and a series of statements describing how these will be resolved.

Final database design

This represents the synthesis of the schema from the holistic and the view integration approaches. Note that the two approaches will generate some relations that overlap, and some that are unique to each approach. Your task is to decide which, if any, of these relations are to be included in the final design. Note that the same reasoning applies to attributes as well. You must address any synonym and homonym problems as you go through this synthesis. You may also denormalize at this stage. This should be presented in standard form. Remember to depict all keys, including columns that are both primary and foreign keys.

Physical database design

This represents the design for the data needed to support the relevant functions at Great Lakes Fitness Center. It includes the physical schema and the list of indexes to be used. You need to define each column, specifying its format, (type and length), and whether it excludes NULL

values. Also specify any indexes, and all forms of referential integrity that may apply. This is to be done using the standard SQL notation (for both tables and indexes), as illustrated below:

EMPLOYEE (EMP_NO CHAR(9) NOT NULL,

EMP_NAME VARCHAR(30) NOT NULL,

POSITION VARCHAR(15),

HIRE_DATE DATE,

SALARY NUMBER(10,2),

DEPT_NO CHAR(3),

PRIMARY KEY (EMP_NO ),

FOREIGN KEY (DEPT_NO ) REFERENCES DEPARTMENT)

where

foreign keys must reference another table

multiple foreign keys must be defined separately

concatenated primary keys must list all columns separated by commas.

INDEX NAME_INDEXON EMPLOYEE (EMP_NAME ASC)

Assumptions

This section covers the assumptions that you have made in each of the earlier stages. Include this at the front of the report, so that I can better understand the thinking that went into the creation of various components of the report. Assumptions could cover:

Identification of primary keys (logical design). This is particularly important for associative entities, especially if the concatenation of keys from the participating entities is not sufficient to serve as a unique identifier.

Identification of foreign keys (logical design). Particularly for 1:1 relationship.

Mapping of subclass structure (logical design). Include justification for option selected.

Relations/attributes discarded in final design synthesis. This is an obvious one. This should address synonym and homonym resolution as well.

Table layout. As a general rule, keys should appear at the front. Composite attributes must be presented together. Other columns may be arranged as you see fit.

Column formats. More than the column type option, this should address the length.

Indexes selected/omitted. Provide reasoning/explanation as to why you felt that is important to create an index on this column (or set of columns).

Grading Criteria

I intend to grade the report on the following criteria:

Correct mapping of entities/relationships to relations. Every entity and relationship in the data model must be accounted for.

Inclusion of all attributes. Attributes should be consistent with the E-R diagram. No new attributes are to be included, and existing attributes should not be dropped.

Correct identification of keys. Each relation must have a unique primary key. All foreign keys must be designated as such.

Proper synthesis of two designs. In case you decided to drop relations (tables) and/or attributes from the two schema, this should not represent fundament al data that is needed by the system.

Proper formats for fields. This is aimed at ensuring that the fields are of the right type and of

meaningful length. A street address of length 10 is just as unacceptable as one with length 100.

Proper use of indexes. Self-explanatory.

Report organization and presentation. Neatness and proper organization counts.

Bin Mates is 4 Cenerakes Signup 0.1 Type PK) Emplayee (PK) tal Time SicrTo Product ass Signup lassTyue Bus Adm 434 Spring 2018: Partial Solution for Project Report Enterprise Data Model Page: 2 Bin Mates is 4 Cenerakes Signup 0.1 Type PK) Emplayee (PK) tal Time SicrTo Product ass Signup lassTyue Bus Adm 434 Spring 2018: Partial Solution for Project Report Enterprise Data Model Page: 2