Connolly and Begg (2015) defined Database as a collection of related data and a database management system (DBMS) to be the software that manages and controls access to the database. A database is also defined as a self-describing collection of integrated records.
This project is built around the principle of creating a Database for Billy Joes Hardware Suppliers which Supply a broad range of hardware and gardening equipment for trade and public. Connolly and Begg (2015) stated that there are advantages in implementing a database management systems, it attempts to eliminates the redundancy by integrating the files so that multiple copies of the same data are not stored; data consistency it reduces the risk of inconsistencies occurring; more information from the same amount of data with the integration of the operational data; sharing data, improved data integrity; improved security and enforcement of standards.
The SQL allows the table to be created and produced with CREATE and SELECT statement, to contain duplicate rows, it imposes an ordering on the columns, and it allows the user to order the rows of a result table.
An SQL statement consists of reserved words and user defined words. It must be spelled exactly as required and the user defined are made up by the user but should follow the SQL requirement. The SQL statement the letter can be typed in upper or lower case.
This project the Database named mina was created under DDL statements which were used to create the six tables, consists of Products Table, Order Details Table, Employees Table, Customers Table, Orders Table and Suppliers Table. The Author’s used the DML statements to populate the tables with values and queries.
The author’s had used Data Manipulation to execute SELECT statement – to query data in the database; INSERT statement – to insert data into a table; UPDATE statement – to update data in a table; and DELETE statement or DROP statement – to delete data from a table.
The Tables are made up of primary keys and foreign keys. The Order Details has an Order ID int foreign key references Orders (Order ID) and Product ID int foreign key references Product (Product ID);
The Product table is formed of Supplier ID foreign key references Supplier (Supplier ID); and the Orders table is composed of Customer ID int foreign key references Customer (Customer ID); Employee ID in foreign key references Employee (Employee ID).
This database includes Create Views in Product list; Customer List WHERE Country Ireland; Current Order List; Employee List the criteria WHERE Clause were used to retrieve all Customers who live in Ireland, employee joining date, order date, product average price , product date of expiry and supplier name was stated.
Also queries in Customer table INNER JOIN with Orders table on Customer ID, First Name, Last Name, Order ID and Paid field; LEFT JOIN from Customer and Orders includes not only rows that have the first name, last name, but also those rows of the first (left) table Order ID, total amount, that are unmatched with rows from the second right table. The columns from the second table are filled with NULLs; RIGHT JOIN includes not only rows that have same first name, last name, from Customer table but also rows of the second right Orders table, Order Id and Paid fields that matched or unmatched with rows from the first left table.
Connolly and Begg (2015) suggested that queries involve the SELECT statement with the purpose in retrieving and displaying data from one or more database tables. It is the most frequently used SQL command.
Furthermore the author’s had created Stores Procedures on Customer table with the purpose of getting the customer details; created Procedure Add Supplier table; and ALTER Procedure in Customer table uspGetAddress.
The database includes 3 aggregate functions: a) SELECT COUNT– returns the number of values in a specified column from Customer table; b) SELECT MAX– returns the largest value in a specified column; c) SELECT SUM– returns the sum of the values in a specified column.
In addition the author’s had created more functions in Product Table SELECT * FROM Customer C, Orders O WHERE O.Customer ID = C.Customer ID;
The author’s Create Function Customer Purchase with Customer table and INNER JOIN from Orders table on Customer ID WHERE first name, last name, and order date were all executed.
Also the author’s had create two user define functions udf that ADD three values it executed successfully; but the second function did not executed successfully user defined function order date & delivery date error msg “ Msg 137, Level 15, State 2, Procedure udfDeliveryTime, Line 5 Must declare the scalar variable “@TimeDifference”.
This project provides two triggers for inserting and updating values. The author’s had created two users for the database in the names of Damien and Mina;
The author’s had summarize and provide information into Database Diagram which describe the relationship or mapping of the project.
- Orders Table links to Employee Table have many to one relationship; one employee can have many orders; and one big order can be handle by many employees.
- Orders Table connected to Customer table have again many to one relationship;
- Orders Detailed Table links to Orders Table there is also many to one relationship;
- Supplier Table connected to Product Table is one to many relationship;
- Order Details Table links to Product Table have one to many relationship.
Database Relationship Diagram
Overall the conclusion of the author’s contribution in creating a new database for Mr Billy Joes Hardware Suppliers it is beneficial for the company in eliminating the manual paper-based system, it will reduce the input error, increase more efficiency and transparency.
Connolly Thomas and Begg Carolyn, 2015. Database Systems A Pratical Approach to Design, Implementation, and Management. 6th ed. Pearson Education Limited Edinburgh Gate England.
Kettle, Damien (2015) Notes SQL Statement Creating Table, Views/Queries, Stored Procedures, Functions, (B8IT101) Dublin, Dublin Business School, on 28 September 2015.