SQL Final Project - Jason Muhle's E-Portfolio



SQL Final ProjectJason MuhleCPT322 – SQL FundamentalsFebruary 7, 2014Richard CandelarioSouthwestern College Professional StudiesSQL Final ProjectFor the final project in this class, SQL Fundamentals, we are tasked with building a database containing 6 tables. Each table has a relation with 1 or more of the other tables in the database, and this paper is to explain how we built our script, and how the tables are related in this database. We will discuss the Primary Keys, the Foreign Keys, and the relations between each, as well as how those relationships will help with referential integrity. We will also discuss the order the tables must be made in for this database to work correctly. Table Construction Order and RelationshipsFor the database in question, there are six tables that need to be created. These tables are as follows: Customers, which holds the customer information, Shippers, which holds the shipper information, Employees, which hold the employee information, Orders, which holds information about the total order, OrderLineItems, which breaks down the order to show what was ordered, and finally Products, which describes each product that could be ordered.For the order these tables must be created in, let’s start at the top. The first 3 tables could be created in basically any order, as they do not rely on any other tables to be created. That would be Customers, Shippers, and then Employees. These tables all have only 1 Primary Key, and no foreign key relationships, so they can be created in any order, but must be created before moving on to the next table in my script. You could also create the Products table at this point as it also has only Primary Keys, 2 in this case, with no foreign key relationships inside of it. In my script I saved it for later, as I worked basically left to right on the diagram. The next table, after creating the first 3, that you’ll want to create is the Orders table. This table has one primary key, OrderID, and 3 foreign key relationships. Those are the CustomerID, from the Customers table, the EmployeeID from the Emloyees table, and the ShipperID from the Shippers table. These are foreign keys because you are pulling the information from other tables as opposed to entering all new information. These relationships are 1-Many because they come from 1 entry in the first table, and can be entered multiple times in the new table, in this case Orders. What I mean when I say that is you can have multiple OrderID entries, with the same CustomerID because you want one customer to be ordering multiple times. In my script I next create the Products table, because of its relationship with OrderLineItems, the last table I create. OrderLineItems has 3 foreign key relationships, 2 from Products and 1 from Orders. Because of those relationships, OrderLineItems has to be the last table created, in order for the foreign keys to already exist. Referential IntegrityLet’s now discuss Referential Integrity. According to Microsoft’s TechNet site, Referential Integrity is defined as “a system of rules that ensure relationships between rows in related tables are valid and that you do not accidentally delete or change related data.” In our case, the integrity of our tables relies on the primary and foreign key relationships. Let’s take the CustomerID from the Customers table for an example. When you are inputting a new order, if you do not have the customer information in the Customers table, you will get an error when using a new CustomerID. For another example, if you input the incorrect CustomerID, you may be sending the items to the wrong customer. The last example for referential integrity involves deleting data. If you were to delete a customer who has previously ordered some products from your company, you then would not be able to find that customers information for shipping out his order or where to send the bill. The integrity of your data is vital, and the relationships are key to making sure your data is correct.ConclusionIn conclusion, we’ve gone over the order the tables must be created in. We’ve also discussed why this order is important, and talked about the relationships of the tables that creates that importance. We’ve also discussed the primary and foreign keys that are present in each table and how those relate to data in other tables. Lastly we went over referential integrity and how it can affect your database if you are not careful when entering new information into your database.References BIBLIOGRAPHY Microsoft. (n.d.). TechNet. Retrieved February 2014, from Microsoft SQL Server: , P. J., & Last, M. Z. (2009). A Guide to SQL. Boston: Course Technology.unk (Director). (2011). Table Relationships [Motion Picture]. Retrieved from TABLE CUSTOMERS(CUSTOMERID CHAR(2) PRIMARY KEY,CUSTOMER_NAME CHAR(50),STREET CHAR(15),CITY CHAR(15),STATE CHAR(2),ZIP CHAR(5),PHONE DECIMAL(10,0) );CREATE TABLE SHIPPERS(SHIPPERID CHAR(3) PRIMARY KEY,SHIPPERNAME CHAR(35),STREET CHAR(15),CITY CHAR(15),STATE CHAR(2),ZIP CHAR(5),PHONE DECIMAL(10,0) );CREATE TABLE EMPLOYEES(EMPLOYEEID CHAR(3) PRIMARY KEY,FIRSTNAME CHAR(15),LASTNAME CHAR(15),SSN DECIMAL(9,0),HIRED CHAR(2) );CREATE TABLE NEWORDERS(ORDERID CHAR(4) PRIMARY KEY,CUSTOMERID CHAR(2),EMPLOYEEID CHAR(3),SHIPPERID CHAR(3),SHIPADDRESS CHAR(50),SHIPDATE CHAR(6) CONSTRAINT CUSTOMERID FOREIGN KEY REFERENCES CUSTOMERS.CUSTOMERID,CONSTRAINT EMLOYEEID FOREIGN KEY REFERENCES EMPLOYEES.EMPLOYEEID,CONSTRAINT SHIPPERID FOREIGN KEY REFERENCES SHIPPERS.SHIPPERID );CREATE TABLE NEWPRODUCTS(PRODUCTID CHAR(5),PRODUCTNAME CHAR(30),QTYPERUNIT DECIMAL(4,0),UNITPRICE DECIMAL(6,2),INSTOCK DECIMAL(4,0),ONORDER DECIMAL(4,0),PRIMARY KEY (PRODUCTID, UNITPRICE) );CREATE TABLE ORDERLINEITEMS(ORDERID CHAR(5),ORDERSEQUENCE CHAR(5),PRODUCTID CHAR(5),QUANTITY DECIMAL(4,0),UNITPRICE DECIMAL(6,2), CONSTRAINT ORDERID FOREIGN KEY REFERENCES NEWORDERS.ORDERID,CONSTRAINT PRODUCTID FOREIGN KEY REFERENCES PRODUCTS.PRODUCTID,CONSTRAINT UNITPRICE FOREIGN KEY REFERENCES PRODUCTS.UNITPRICE); ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download