RELATIONAL DATABASE CASE STUDY

 Print

Imagine that you work for a data service company that specializes in data storage, data processing, and data streamlining for public libraries. The current implementation is a file based system with all data stored in a single spreadsheet. As the company expands the business to serve more libraries, it becomes obvious that the current model does not meet the ever increasing demand.

You have been assigned the task to design a new relational database to replace the current file based storage. It will be a single centralized database to store information needs for all libraries. Some key information to be stored includes data about customers, libraries, catalog, physical items, and check in/out transactions.

Additional general business background information is listed below:

a. Each library system operates independently. Each library system issues its own library card to its customers.

b. A customer can have multiple library cards, one for each library system. For example, a customer can have both Baltimore library card and Annapolis library card. Different library systems may issue cards with the same number.

c. A library has many branch locations. Customers visit a branch to check in/check out items.

d. Different libraries share generic catalog information. Currently there are two types specific catalog items – book and DVD.

e. Each branch stores and maintains physical items. General information about each physical item can be found by checking catalog. There could be multiple copies for the same title so each copy needs to be stored separately. A branch uses a copy number to identify a particular copy so that to keep track of information such as purchase date.

You were provided with a sample data stored in the current spreadsheet format as attached. Please be aware that it is possible that a customer has never checked out any item (as shown line 23), and it is possible that an item has never been checked out yet (as shown line 24 through 67).

Below is a list of your specific tasks:

Step #1: Analyze the current model

Print

1. What are some of the major issues with the current file based storage model?

2. Are there data anomalies with the current solution? Can you provide specific examples of INSERT/UPDATE/DELETE anomaly?

3. If you design your database strictly based on the current model (meaning one table to store all information), would it be considered as normalized? Would it be in 1NF, 2NF, and 3NF?

4. If not, describe and illustrate the process of normalizing it to 3NF. Identify functional dependencies and create dependency diagram before each conversion.

5. Show the final table structure after normalization and make sure it is in 3NF.

Step #2: Document and formalize your final design

Print

1. Identify entities and attributes. List entity name and attribute names for each entity.

2. Identify relationships among entities. List relationship type in terms of cardinality and specify business rules (e.g. 1:M between Library and Branch: a library has one or many branches; a branch belongs to one and only one library).

3. For each entity, identify primary key and foreign key if applicable. If you use surrogate key for PK, also specify business unique key (natural key). For FK, also specify parent entity and the matching attribute in parent entity.

4. Create ERD using crow’s foot notation using ER Assistant or Visio. The ERD should incorporate all items mentioned in 2.1, 2.2, and 2.3 (NOTE: 2.1, 2.2, and 2.3 still need to be answered separately in narrative format).

Step #3: Implement the database

Print

1. Write SQL DDL statements to create tables in Oracle to implement all entities, attributes with the correct data type and data size, primary keys, foreign keys, and constraints such as NOT NULL.

2. Create indexes on all foreign key and business key columns.

3. Write SQL DML statements to insert all data shown in the sample spreadsheet into the new database.

Step #4: Use the database and create report

Print

1. Write SQL queries against each table so that to display number of records in each table.

2. Write a minimum of 10 queries to query the database. Add comment to each query to describe the purpose.

3. Write SQL statement to create a database view so that a report can be generated by querying the view to create the same set of data as shown in the original spreadsheet. Ideally the view is created by querying base tables directly. However, it is acceptable if it is based on other views but with only one nested level (the final view is against other intermediate level view(s), and each intermediate level view is a query against base tables directly).