Design Relational Database System Assignment Help
In today’s digital world, it is any business to have a presence at the online platform. Today, every business is working well as online. Online presence of the business multiple its profit to several times. E-commerce is the best way to reach targeted customers easily, cover customers across the globe give easy access to customers to make a purchase, easy transactions, simple transfer of money, tracking delivery, and after sale services. The database is created with the help of query language known as Structured Query Language (SQL). This language is used to perform CRUD operations on the relational database.
The above is the ERD (Entity Relationship Diagram) of the database for a Fashion Outlet. The ERD consists of six entities according to the user and system requirement. The ERD covers all the entities which are required for the proper functioning of the e-commerce website (Chapple, 2019). The description of each entity is as follows:
- This entity holds all the information about the customer/user of the online e-commerce
- It holds details like customer_email, customer_id, customer_name, customer_mobile, customer_address, password, and customer_username.
- The attribute’s names give information about the kind of data they hold. It has two primary keys, one is customer_email and second is customer_id.
- It has no foreign key.
- The primary key customer_email is a foreign key for the Payment_Details entity and the primary key customer_id is a foreign key for Shopping_Basket
- This entity holds information about the category identification of the product.
- The category_id is the primary key for this entity and foreign key for the Product_Details
- This entity has attributes to hold payment related details. It has attributes checkout_id, basket_id, customer_email, and payment mode. It is evident from the names of the attributes, about
- The primary key checkout_id is a foreign key for the Shipping_Details
- This entity represents the shopping basket or the table for the shopping It has attributes basket_id, product_id, customer_id, and total_price.
- It has one primary key basket_id.
- It has two foreign keys product_id and customer_id.
- The primary key basket_id is a foreign key for Payment_Details
- This entity represents the shipping details for a particular customer. It has attributes shipping_id, checkout_id, shipping_address, shipping_date.
- The shipping_id attribute is the primary key.
- The checkout_id is the foreign key.
- This entity represents the product table and provides details or specifications about the product.
- It has five attributes product_id, category_id, product_name, product_price, and product_quantity.
- Product_Id is the primary key.
- It has one foreign key category_id.
- The primary key product_id is the foreign key for Shopping_Basket
Creation of the database becomes an easy task, after designing the ERD diagram for the database. The database is created using a query language known as Structured Query Language (SQL). A fully functional database system is the system in which relationships between the tables are clearly defined. These relationships are defined using the primary keys and foreign keys.
SQL is a standard language used to develop and query relational databases. SQL is the query language with work with the structured data. Structured data is the data which is structured in the format of rows and columns and there is a certain relationship defined between the tables of the database (Hopping and Millman, 2019).
Below is the execution screenshots of all the SQL queries which are used to create a database according to user and system requirements:
The above SQL query “create table <table-name> (attribute1, attribute2, …)” is used to create a table named Customer. It has attributes like customer_id, customer_email, Password, etc. The attributes are defined with their data-type and respective length and a constraint. The customer_id and customer_email are defined as primary keys.
In the above image, a table is created for Customer details in which there are attributes created to store the details.
The above SQL query is the same as a query to create a table Customer. The name of the table is Category. It has only two attributes category_id of data-type varchar, length 45, and the constraint that it should be Not NULL, and product_type of data-type varchar, length 45, and constraint that it should be Not NULL. The category_id is defined as a primary key in this table.
In the above image, a table is created for Category details in which there are attributes created to store the details of the type of product which are categories in two parts: Rent and Sales. The values can’t be null for the attributes which are category_id and product_type. In this category_id is the primary key and has unique values.
The above SQL query creates a table name Product_Details. It has five attributes with their data-type and constraint.
In the above image, a table is created for Product details in which there are attributes created to store the details of the product. The values can’t be null for the attributes which are product_id which contains the unique value & every product is assigned with different id, product_name contains the name of the product & accepts the length of 45 characters & the data type is varchar, product_quantity consist of a number of products to add at a time to cart & the user can enter the value of 2 digits in it and this is of int data type, product_price contains the price of the product which has the data type of int & accepts the value until 11 characters and the last attribute is category_id consist of types such as rent and sales.
In this diagram, a table is create Basket details in which there are attributes created to store the details for the shopping cart. . The cart takes costumer_id and total_price to produce the desired result. Total price is stored in total_price attribute.
In this diagram, a table is created for Payment details in which there are attributes created to store the details for payment. There attribute which takes payment which is stored in payment_mode.
Execution of query:
Figure 10: Screenshot of executing query 5
Shipping Details Table Query:
Figure 11: Query for a shipping table
In this diagram, a table is created for shipping details in which there are attributes created to store the details for shipment. In this attributes are shipping_id which is unique and is not null, payment_id is used to accept the payment, shipping_address is used to add the address details to which the shipment is to be done and shipping_date contains the expected delivery date.
The database store the structured data in tables (in the form of rows and columns). SQL is the language used to perform CRUD operations on the database. These operations are performed on the database by executing queries on the database. These queries are implemented by the user from the UI provided to it. The backend passes the query to the database and the database process or executes the query on the data stored in the database. There are different queries which the user might run on the database. Some of the queries are select, insert, delete, drop, etc. These queries help the user to extract the desired information from the database or change the data present in the database (Ballard, 2019).
Recommendations to improve database development:
- Always create an ER diagram development process
- A beta version of the database must be created and tested developing a production version
- The performance of the database can be improved the queries executed on the database
- Allocating more memory to the database increase ease the development process of the database
LO4 Produce technical and user documentation
The end-user need not to play with queries, the backend implementation of the website will interact with the interface and the end-user is provided with an easy graphical interface to interact with the backend. The user and technical documentation guide the user to walk through the website (Cooley, 2017).
The database is a crucial overal process of the website or web application. In the project, an ER diagram is designed prior development process of the database.
The required database is successfully designed and developed for an e-commerce website.
Ballard, B. 2019. Back to Basics: Writing SQL Queries. [Online] Available at https://thoughtbot.com/blog/back-to-basics-sql
Chapple, M. 2019. Entity-Relationship Diagram. [Online] Available at https://www.lifewire.com/entity-relationship-diagram-1019253
Cooley, S. 2017. Why User Documentation Is Important. [Online] Available at https://www.deque.com/blog/user-documentation-important/
Hopping, C. and Millman, R. 2019. What is a relational database? [Online] Available at https://www.itpro.co.uk/data-insights/31725/what-is-a-relational-database
Manomaya, T. 2016. What is the importance of a database management system? [Online] Available at https://www.manomayasoft.com/blog/item/210-what-is-the-importance-of-a-database-management-system
Links for Us