Project Deliverable 3: Database and Programming Design

CIS 498: Information Technology Capstone

DATABASE DESIGN

Itech is a business that deals with computer and ICT solutions. They intend to start an E-commerce business where they will be selling things online. The idea is to make sure that they cover all people everywhere in all areas without limitations on the location. Some of the solutions that Itech intends to offer to its customers are such as Laptops, TP-Links, Servers, Integrated solutions, Antiviruses, Desktops, Tablets, and Smartphones among others (Westerman & Bonnet, 2015). Just like any other business, Itech will have to choose a platform on which they are going to control their E-commerce business. The project planning and initiation phases has been outlined and submitted earlier. The next phase is the database design phase. The database design includes the database schema with normalized tables, entity relationship diagrams and data flow diagrams for the database of the website. E.F.Codd developed Relational Database Management System (RDBMS ) to make better database management systems. It is the most common and popular method to store data today.in relational database management system, the data is stored in the form of tables with the tuples (rows) and Attributes ( columns).The company plans to use relational database management system (RDBMS) to store and save information for future use. The Relational Database Management System is a collection of tables connected with foreign keys. The aim of using database in the organization is to store raw data in the form of meaningful information. Eg; “25” is a number. But if we write Age 25. This is now a piece of information. The information stored in the database of the company is very important and is used for strategically decision making in the company.

Database schema

The schema of the database represents the blueprint, or the structure of the information stored in the database. The schema of the database includes the structure of the tables not the content of the table.

Example:

Schema of a product table

Product id (Number)

Product name(Varchar)

Product description (Varchar)

Category (Varchar)

Cost (Number)

The above layout depicts the schema of a table for the products. Schema for the database includes the structure of the tables to be included in the database for storing the information. The schema of the database represents the database structure logically. It clearly states the overall structure of the database for programmer.

Keys and Referential integrity

The relational database is connected by the relations between the table sin the form of keys.

Primary key: The primary key in the table is a field which uniquely identifies the column. In this case, the product id is a primary key in many tables along with customer id in customer table and order id in order table. The primary key field will never accept a null value or a duplicate value.
Foreign key: When a primary key of one table is used on another table it is called as foreign key. It ensures that no record is added to the foreign key table unless there is a record in the relational table. If the record is deleted in one table, it is also deleted in the related table.

In appendix A, the database schema is depicted. The product id is a primary key on the product table and foreign key in the order table. The customer cannot place the order if product id does not exist. Similarly, if admin deletes a product id from the database and there is an order it will be cancelled.

This is called as referential integrity. This ensures that database is consistent. No user can enter wrong or duplicate information. A customer cannot place an order for the product where product id does not exist. If product id is deleted and customer places an order for the product, it will be a problem? Using referential Integrity, it can be ensured that customer cannot place an order for the product whose product id has been deleted.

The referential integrity helps in maintaining the consistency in the database and ensures safety of data.

Normalization

The information stored in the database should be non-redundant. The duplicate information will lead to anomalies in the database at the time of insertion, updating and deletion. The Normalization process ensures that the information stored in the database is non redundant. It ensures that information maintained across the database is updated.

The Normalization include 1 normal form, 2 Normal Form, 3 Normal form, 4 Normal Form, 5 Normal Form and Boyce Codd Normal from (BCNF). Generally, Normal forms are implemented till 3 Normal forms. Third Normal Forms ensures that there is no duplication of data and database is free from insertion, updating and deletion anomalies.

First Normal Form: The first normal form consists of two rules:

No null values
Every field must have a single atomic value.
No attribute must take multiple values. (Korth & Silberschatz, 1991)

The Category tables

Product id	Category
1	laptop
2	Smartphone
3	Tablet
4	Laptop

Second Normal form:

A relation is said to be in second normal form when it is in first normal form and all non-key attributes are dependent on the primary key. To achieve second normal form in the database structure, the product and category two tables were created to ensure all the non key attributes are dependent on the key attribute in the database. (Appendix A).

Third Normal form:

A relation is in Third normal form when it is in second normal form and no transition dependency exists in the table. There must not be any field in the table other than primary key which can transitively dependent on each other. Therefore, the tables need to be divided to ensure the consistency of the database to ensure dependency only on the primary key and to remove the transitive dependencies. Appendix A depicts the tables are free from any transitive dependencies (Elmasri & Navathe, 2013).

Queries

Two

Select Orders. Order_id, Customers.name from Customers Inner Join orders on customer .customer_id = order.order_id;

It will display the result combining the order id and customer names by matching the order id and customer id.

If you want to find out the orders by a particular customer, then

Select customer_name from Customer From sales group by Customer_name where Customer_name = ‘charles’;

Entity relationship diagram

Entity relationship diagram helps in depicting the relationship between the tables and attributes in the relational database. It is very easy to convert a relational database into an E-R diagram or vice versa.

The notation popularly used for E-R diagram is:

Entity: Entity is represented using a rectangle.

Attributes: Attributes are the properties of entities. They are depicted using the ellipses. Attribute represents one property and is connected to entity.

Relationship: Relationships are represented using the diamond box and entities are connected to the diamond box. The type of relation is written inside the box.

Appendix B shows the Entity relationship diagram for the database design.

An E- R diagram can be easily converted into a table while designing the database.

Entities are converted into the tables and attributes are converted to the fields or columns in the table. The relationship between the entities are managed using the primary key and foreign keys in the tables. (Appendix B)

Data Flow diagram

The data flow diagram (DFD) is used to show the flow of information and processes in the entire system. It depicts the flow of information from each entity. It serves as a powerful tool in detecting any problems at the design phase. Like E-R diagram, DFD uses a common notation to depicts the entire system using rectangles, arrows and route between the entity and the system. Unlike E- R diagram, DFD can show various levels of information. DFD can show detail up to three levels. 0 level, 1 level and 2 level DFD, depending on the detail of information included in the DFD. 0 level or context level DFD shows the working of entire system along with its entities. Appendix C shows the context level DFD.

References

Westerman, G., & Bonnet, D. (2015). Revamping your business through digital transformation. MIT Sloan Management Review, 56(3), 10.
Korth, H., & Silberschatz, A. (1991). Database system concepts. Singapore: McGraw-Hill.
Elmasri, R., & Navathe, S. (2013). Database Systems. Pearson India.