Database Design Project Document Shell (Coursework Sample)

Database Design Project Document Shell
Fundamentals of Database Systems CS251-1503A-02
Oracle Database
Professor
Table of Contents
TOC HYPERLINK \l "_Toc424579702" \h Description of the database design life cycle (week 1 IP) 3
 HYPERLINK \l "_Toc424579703" \h The Entity Relationship Diagram (Week 2 IP) 6
 HYPERLINK \l "_Toc424579704" \h The Logical Model and Normalization (Week 3 IP) 9
 HYPERLINK \l "_Toc424579705" \h The Microsoft Access Database (Week 4 IP) 14
 HYPERLINK \l "_Toc424579706" \h The Microsoft Access Database Application (Week 5 IP) 17
 HYPERLINK \h References 18

TOC
Description of the database design life cycle (week 1 IP)
Oracle Description
Oracle SQL is software developed by Oracle Corporation to offer data management solutions over different platforms. Some of the platforms include devices such as a mobile device, a personal computer, data centers, and data clouds. It uses the relational database models and a graphical user interface. Other tools used on the server include JavaScript and Java. It offers mission-critical performance, in-memory technology boost for better performance, data storage solutions, and a flexible IT space that works across different platforms as mentioned earlier. The latest version is the following:
12c Release 1 (12.1.0.2) / 22 July 2014; 11 months ago
In this relation, the Oracle database is designed in the following way: tables are arranged in rows and columns that cross to be used for the further development of the dominance of integrity and overall coexistence with the need to develop the possibility of being connected to store data adequately to the ability of getting involved in the meaning: the database serves for the safe data storage and preventing the unauthorized access to the server.
DBDSL Steps
Planning
It is the most important phase of the development cycle. The phase includes activities that are focused towards identifying the required features and the preferred operations procedures for the database. Analysis of the business rules, business processes, process diagrams and the desired output of the system are outlined in this phase. The project plan is laid in this phase and a given time frame is identified for the different lifecycle processes. Project feasibility is carried out at this stage of the process. A systems analyst is usually the expert providing the guidance at this stage.
Analysis
It is the step where the solution to the problem is identified, and project goals are laid out. The functions and operations of the program are explained. Some of the tools used in this process by the system analyst include structured analysis and CASE which is a computer-aided engineering software. Any adjustments to the budget are identified at this stage.
Design
Tangible results of the previous steps are seen at this stage. They include the parts and features of the system, the process diagrams, and the screen layouts. The logical systems design is shown, and the physical side of it is displayed. The scope of the project is identified at this stage.
Development
The stage marks the end of the initial stages of development. Experts at this stage include the programmers, database specialists, and network administrators. The stage is where the application is developed and put into reality. Training of the officers of the organization is done here. Installation of the developed database is also done.
Testing
It involves using the system in advance before launching it. The system is fed with data so that to check whether it is working. A unique environment is chosen for this. Error checks are made to identify the bugs and hitches in operation and hence correct them.
Implementation
The system is ready for the actual environment, and an installation process is chosen for it. The phasing out technique is chosen, and the old data is fed into the new system. The training of employees is also done at this stage.
Maintenance
The last stage of the cycle which includes making up for the changes, making corrections to the system and any required alterations are made at this stage.
Three Steps in Database Design
Conceptual modeling
It involves constructing the data model for the problem at hand. The process requires the analysis of the data types, their relationships, and constraints. It involves ER modeling and checks for redundancies in the system.
Logical modeling
It involves designing the model of information. Every system uses information and gathers information by use of different techniques such as forms and tables. It involves table generation and normalization.
Physical modeling
It includes configuration of the storage media. Memory management and indexing is another process. The stage handles development of query processing.
The Entity Relationship Diagram (Week 2 IP)
An Entity Relation Diagram for purchase order application


Discussion
The above diagram shows a conceptual model of the activity of purchasing an order by a customer. There are various entities, and their attributes are highlighted in the ERD model shown above. The entities involved in the purchasing process include; customer, purchase order, stock item and line item. A customer will place a purchase order that consists of stock items that will be listed as the line item (Jorgensen, 2011). The line item will be the complete order of the customer. The rectangular-shaped boxes show the entities in the ERD. The oval shaped boxes show the attributes of the entity. The key attribute in each entity is the uppermost attribute of each entity. The relationship between entities is represented by the parallelogram-shaped/ rhombus-shaped boxes. They elaborate the relation connecting two or more entities. The shapes along the relation lines show the number of entities that can be connected to another (Coronel et al, 2008).
The first entity in our ERD is the customer. The customer entity is identified by various attributes. The customer number is the primary attribute that is unique to each customer and thus identifies each customer. Other attributes that identify the customer include the customer name, the residence state of the customer, the street of the customer, the zip code of the customer and the phone number of the customer. The non-primary attributes may be shared by the customers, for example, two customers may come from the same state or the same street (Jorgensen, 2011).
The customer places a purchase order. The relation between purchase order and the
customer is placed


figure 2
figure 3
The figure 2 above shows that only one customer can place a purchase order. The figure 3 shows that a customer can place many purchase orders. The purchase order entity is identified by various attributes. The key attribute that identifies the purchase order is the purchase order number. It is denoted by p_o_number. Other attributes that identify the purchase order entity includes; the customer number, order date, ship date, street, state and the destination’s zip code. A purchase order may refer to one item or may consist of various items (Panneerselvam, 2011).
The purchase order entity may be related to the stock item or line items. One purchase order may consist of one or many stock items. Conversely, one stock item can be present in various purchase orders. The relation between the purchase order and the stock item is that the purchase order refers to stock item. There are various attributes that identify the stock item. The stock item number denoted by stock_item_number in the ERD above is the key attribute of the stock item entity. Other attributes that describe the stock item includes the description and price.
Each order in a company is processed as a line item so as to constitute the quantity and the discount. The stock item is thus related to the line item entity in our ERD. A stock item can be related to one or more line stock item. The line item entity is identified by various attributes. The primary attribute is the line item number. Other attributes of the line item entity include; the quantity, the stock item number, purchase order number and discount allowed.
The Logical Model and Normalization (Week 3 IP)
Logical Data Model
TableAttributeTypeCustomercustomer_numberPrimary Keycustomer_namezip_codeForeign Keyphone_numberPurchase Orderp_o_numberPrimary Keycustomer_numberForeign Keyorder_dateship_datezip_codeForeign KeyAddressZip_codePrimary KeystreetcitystateStock Itemstock_item_numberPrimary KeydescriptionpriceLine Itemline_item_numberPrimary Keystock_item_numberForeign keyquantitydiscount
Table 1: Logical Data Model


Figure 1: Logical Data Model
First Normal Form (1NF)
According to 1NF, there should be no two records of information having duplicate group of data. Every arrangement of the column ought to contain a singular record; so numerous columns cannot be utilized to retrieve the same record. Every table must be sorted out into rows, in addition, every column must have a primary key, which recognizes it as unique.
Consider the table below which is not in First Normal Form.
Product NameCustomer_namecustomer_streetphone_numberOrder_dateShip_dateTo_streetPhone, bookJamesMain_street+1800233274202/02/201402/04/2015Main StreetTable: Purchase Order Table
In First Normal Form, I separated the data into several rows.
Product NameCustomer_namecustomer_streetphone_numberOrder_dateShip_dateTo_streetPhoneJamesMain_street+1800233274202/02/201402/04/2015Main StreetBookJamesMain_street+1800233274202/02/201402/04/2015Main Street
Table: Purchase Table
Second Normal Form (2NF)
According to the 2NF, there should not be whatsoever p...