CSCI343-Project 1 Relational Database Solved

Introduction
The purpose of this project was to create a simple relational database utilizing Oracle’s SQL Developer and Data Modeler tools as well as various beginner level concepts from the course text Databases Illuminated. Per the assignment page on the course moodle, the project requirements are as follows:

1.    Provide an explanation of my experience in completing this project.

2.    Create and provide a data dictionary.

3.    Develop and provide a logical data model in Bachman notation.

4.    Develop and provide a relational model.

5.    Generate and provide a DDL script for creating our database (with SET ECHO ON)

6.    Provide sample SQL Inserts that were used to input data in our database.

7.    Provide example SQL Selects to prove our database is functioning as intended.

Implementation
For this project I decided to utilize ’Project 3’ from the potential project pool. This project seeks to develop a database to be used by a small-scale car dealership. The project consists of several tables which serve to provide a working product which accounts for many of the needs a car dealership might have. The tables are as follows:

1.    CarCustomer - which stores information pertaining to the customer of the dealership

2.    CarSalesperson - which stores information pertaining to the sales employees of a

dealership

3.    CarNewCar - which stores information pertaining to the new car inventory of a deal-

ership

4.    CarSale - which stores information pertaining to the sales made by employees of the

dealership

5.    CarManifest - which stores the primary keys of both the CarSale and CarNewCar tables in order to facilitate their M : N relationship.

6.    CarRegistration - which stores information relating to registration of a sold car.

7.    CarWarranty - which stores information describing the warranty purchased for a sold

car.

8.    CarSaleSurvey - which stores information relating to customer satisfaction.

Each table contains the appropriate primary and foreign keys and the totality of this project satisfies all of the technical requirements outlined in the introduction.

Mastery of the basic design concepts as required by this project are all demonstrated within my data dictionary, logical data model, relational model, and resultant DDL script which are all in the appendix at the end of this document.

Developing the data dictionary was perhaps the most daunting process I experience in this project. Initially, I found it difficult to not design my database on the fly as I was capturing the relevant information from the ’Project 3’ document. Finding relevant phrases and business needs was not necessarily difficult but certainly time-consuming. The resultant data dictionary represents an initial assessment of what information might be useful in my ideal database. As I began to design the logical model of my project, it became apparent that in order to implement this data dictionary completely, I would have to greatly exceed the necessary project requirements (I know of at least 18 tables that would have been necessary). In order to keep this project at a reasonable length for implementation and grading, I elected to create a truncated version of my ideal product. I have, however, included the full data dictionary as a testament to the trimming I felt necessary as I completed my logical model.

Using the SQL Data Modeler tool from Oracle was a dream. The interface was easy to grasp and navigate given the in-class tutorials. Having the ability to go from my completed logical data model to the relational model and then to a DDL script in a matter of just a few clicks was nothing short of amazing.

In working with the database itself, again, the in-class tutorials were extremely beneficial. Having completed CSCI-344, I had some experience with the basic SQL statements required but without guidance in-class, navigating the SQL Developer tool would have been a daunting task. Overall the experience with this tool was also positive. A few sample inserts from my database are included below first with using a full insert statement including all the column names (CarCustomer table):

 

And another insert using a truncated format which omits the column names (CarNewCar

table):

 

Errors and Fixes
Though this project was vastly more complex than my previous foray into SQL in CSCI-344, I did not feel that issues encountered were any more or less difficult to deal with than before.

The vast majority of issues encountered in this project were related to an incorrect relational model. Fixing these issues required me to drop all of my tables, delete my relational model from the SQL Data Modeler tool, make the necessary changes in my logical model and then generate everything all over again. Luckily I only had to do this a couple of times and the tools made each time a fairly painless experience.

Using SELECT statements to check for errors and ensure that my table was behaving as it should proved to be a huge help in determining the validity of my design. Below is a

SELECT * of my CarCustomer table:

 

This example is certainly functional, but probably fairly boring to a business owner. I utilized SELECT twice more to generate a couple of reports that I thought might be interesting. The first is a report for the sales team which capture customer names and contact information for customers who elected for the least expensive warranty. Information like this could be given to the sales team during downtime to help drive additional revenue through ’upselling’ by having the salesperson call and reiterate just how much their car actually costs and the benefits of a better warranty:

 

The second report is intended to be used by the owner to followup with customers who had a poor experience with their salesperson.

 

Conclusion
As the first attempt at developing a larger scale project using SQL and these Oracle tools, I am satisfied with the result. Though the scope of this project is narrow, I don’t feel as though the quality of the work or the learning experience suffered as a result. Admittedly, I still found myself throughout the process discovering new techniques that I would like to implement in personal projects. I am certain that my continued efforts and curiosity will grow my fluency with SQL.


Appendix
Initial Data Dictionary:

Customer Records custFirstName - The customers first name custLastName - The customers last name custStreet - Street of the customers physical address custCity - City of the customers physical address custState - State of the customers physical address custZip - Zip Code of the customers physical address custAreaCode - Area Code of the customers primary phone number custPhoneNumber - Customers primary phone number custLicenseNum - Customers drivers license number custAdSeen - Ad that brought the customer to the dealership custSaleMade - Denotes whether a customer has made a purchase Car Records carVIN - Vehicle ID number for a car carMake - Make of a car carModel - Model of a car carPrice - List price of a car carManufactureDate - Date the car was manufactured carColor - Color of a car carDoors - The number of doors of a car carWeight - Weight of a car carManufacturePlace - Place in which the car was manufactured carOptions - List of options specific to a particular car carMileage - Current mileage of a car Salesperson Records salespersonFirstName - First name of a salesperson salespersonLastName - Last name of a salesperson Sale Records saleListPrice - The sticker price from the manufacturer for a car being sold saleFinalPrice - Price at which the car is being sold after negotiation saleTax - Tax to be collected for sale of the car saleLicenseFee - License fee for the car being sold saleVIN - VIN of the car being sold saleCarMileage - current mileage of the car being sold at the time of the sale saleSalesperson - Salesperson who closed the sale saleWarrantyInfo - Warranty information of the vehicle being sold saleFinancingTerm - Number of months at which the vehicle is being financed

Logical Data Model:

 

Relational Model:

 
 

DDL: