Scheme for Creating a Storage Database
Scheme for Creating a Storage Database
As the database team, it was our job to provide a storage facility for the other teams. We had to come up with a design that was flexible enough to handle the various types of data and yet robust enough to make searching and record lookup efficient. Since Oracle is available in the lab and is a very powerful database management system, we decided to leverage our assets and create the database on perhaps the best database server available. But before we could slap together some SQL commands we had to plan some things out first.
Schema
The process of building our database consisted of many phases of analysis before the actual design. We had to create an outline, and distribute it to the groups for feedback. Upon receiving the feedback and suggestions, we constructed the Entity-Relationship Diagram. Then we transformed the ERD into a prototype of the tables in the database and asked other team members for approval. Then we made final layouts of the tables, which consisted of the actual names of the tables and their corresponding attributes. These layouts were distributed to the other teams so that they can incorporate our design into their servlets.
Constructing an ER diagram is very important because the ER diagram is most often used as a tool for communication between database designers and end users during the analysis phase of database development. The process of designing the ERD consists of defining the types of entities: strong entities, weak entities, and the important attributes associate with each entity. Each entity has many attributes associated with it. Examples of data attributes for the customer table are customer_id, customer_name, etc. We composed the data into six entities: customer, order, books_ordered, music_ordered, books, music, and track_list. The symbols at the end of each relationship line, on the ER diagram, represent relationship cardinality and modality. For example, a customer might submit many orders or not submit anything at all, because it's not mandatory for a customer to place an order. In our ER diagrams (figure 1, page 5), majority of our entities are composed of many attributes. For example, shipping information is included in the customer table. One of the advantages of composing many attributes in one table is that we don't have to do joint queries between different tables.
After constructing the ERD, we transformed it into tables. Each...