SQL DATABASE PROJECT
Exotic Cars Inc. (ECI) is a company specializing in sales and maintenance of the exotic (hence the name) cars.
The
cars that ECI sells come from multiple sources: some are shipped
directly from the factories, other purchased from the private
collectors, and yet some other cars are traded-in by the customers of
ECI.
ECI
also provides a regular maintenance service of the exotic cars, such as
engine tune up, tire rotation, oil change, etc. You do not have to buy a
car from ECI to use its maintenance service; consequently, some owners
of the exotic cars purchased elsewhere also use ECI maintenance service.
ECI
appreciates continuing business of its patrons; consequently, the
company wants to maintain as much information about each valuable
customer as it possibly can.
As
a result, ECI diligently records how much money each customer spends on
buying cars (more than a half have purchased two cars and about third
bought three or more), on maintenance service (how much customer spends
overall, and per car), and whether or not a customer ever traded a car
in.
Course Project Description
Your
goal is to develop a database that shall allow ECI to keep track of its
business and provide the management with the following information
regarding the performance of the business:
HR Management
Complete information about ECI employees, especially salesmen and technicians
Inventory Management
Complete information on the number and types of the cars currently kept in stock
Complete information on the number and types of the cars obtained from each source (factory, private collector, trade-in, etc.)
Maintenance Service Management
Complete information on the number and types of the cars that were serviced.
Complete
information on maintenance record of each serviced car (car
information, type of problem, owner, mechanic who performed the
maintenance, and cost of maintenance)
Customer Service Management
Complete information on every customer including information regarding their:
• Total spending on cars
• Total spending on service maintenance
Accounting
Complete information on spending of every customer
Complete information regarding what was paid to each vendor
Complete information regarding what was received by ECI from the sales of cars
Complete information regarding what was received by ECI from the maintenance service
NOTE:
You don’t have to implement all of the required functionality- use the
description above to determine entities and attributes that you data
model must include.
Submission of the Project
You are going to submit the Course Project over the following sequence of steps (a single deliverable per step):
1. Course Project: ERD
2. Course Project: SQL code used to create your DB Schema and populate your DB Tables
3. Course Project: Reports
Course Project Grading Guidelines
Students, your project is worth 100 points total, with the following allocation of points:
Project Component
|
Points
|
Functioning Database SQL code used to create your DB Schema Contents of the DB Tables (minimum 10 records per table)
|
70
|
Functioning reports
(You must be able to run your reports, based on 10 queries of your
preference. 4 queries must involve at least 3 tables, and another 4
queries must involve at least 4 tables.)
|
30
|
Total points
|
100
|
NOTE Regarding the format of your submissions:
The complete package that you will need to submit must include the following:
1. Copy of your database created using SQL Server
2. A Word document/PDF file including the following parts:
1. ERD of your database
2. SQL code that you used to create your schema (copy & paste from SQL Server)
3. SQL code that you used to populate your tables (copy & paste from SQL Server)
4. Description
of your reports- you must clearly explain what information each of your
reports allows you to obtain (you will need to have 10 descriptions)
SQL DATABASE PROJECT
Exotic Cars Inc. (ECI) is a company specializing in sales and maintenance of the exotic (hence the name) cars.
The
cars that ECI sells come from multiple sources: some are shipped
directly from the factories, other purchased from the private
collectors, and yet some other cars are traded-in by the customers of
ECI.
ECI
also provides a regular maintenance service of the exotic cars, such as
engine tune up, tire rotation, oil change, etc. You do not have to buy a
car from ECI to use its maintenance service; consequently, some owners
of the exotic cars purchased elsewhere also use ECI maintenance service.
ECI
appreciates continuing business of its patrons; consequently, the
company wants to maintain as much information about each valuable
customer as it possibly can.
As
a result, ECI diligently records how much money each customer spends on
buying cars (more than a half have purchased two cars and about third
bought three or more), on maintenance service (how much customer spends
overall, and per car), and whether or not a customer ever traded a car
in.
Course Project Description
Your
goal is to develop a database that shall allow ECI to keep track of its
business and provide the management with the following information
regarding the performance of the business:
HR Management
Complete information about ECI employees, especially salesmen and technicians
Inventory Management
Complete information on the number and types of the cars currently kept in stock
Complete information on the number and types of the cars obtained from each source (factory, private collector, trade-in, etc.)
Maintenance Service Management
Complete information on the number and types of the cars that were serviced.
Complete
information on maintenance record of each serviced car (car
information, type of problem, owner, mechanic who performed the
maintenance, and cost of maintenance)
Customer Service Management
Complete information on every customer including information regarding their:
• Total spending on cars
• Total spending on service maintenance
Accounting
Complete information on spending of every customer
Complete information regarding what was paid to each vendor
Complete information regarding what was received by ECI from the sales of cars
Complete information regarding what was received by ECI from the maintenance service
NOTE:
You don’t have to implement all of the required functionality- use the
description above to determine entities and attributes that you data
model must include.
Submission of the Project
You are going to submit the Course Project over the following sequence of steps (a single deliverable per step):
1. Course Project: ERD
2. Course Project: SQL code used to create your DB Schema and populate your DB Tables
3. Course Project: Reports
Course Project Grading Guidelines
Students, your project is worth 100 points total, with the following allocation of points:
Project Component
|
Points
|
Functioning Database SQL code used to create your DB Schema Contents of the DB Tables (minimum 10 records per table)
|
70
|
Functioning reports
(You must be able to run your reports, based on 10 queries of your
preference. 4 queries must involve at least 3 tables, and another 4
queries must involve at least 4 tables.)
|
30
|
Total points
|
100
|
NOTE Regarding the format of your submissions:
The complete package that you will need to submit must include the following:
1. Copy of your database created using SQL Server
2. A Word document/PDF file including the following parts:
1. ERD of your database
2. SQL code that you used to create your schema (copy & paste from SQL Server)
3. SQL code that you used to populate your tables (copy & paste from SQL Server)
4. Description
of your reports- you must clearly explain what information each of your
reports allows you to obtain (you will need to have 10 descriptions)
SQL DATABASE PROJECT
Exotic Cars Inc. (ECI) is a company specializing in sales and maintenance of the exotic (hence the name) cars.
The
cars that ECI sells come from multiple sources: some are shipped
directly from the factories, other purchased from the private
collectors, and yet some other cars are traded-in by the customers of
ECI.
ECI
also provides a regular maintenance service of the exotic cars, such as
engine tune up, tire rotation, oil change, etc. You do not have to buy a
car from ECI to use its maintenance service; consequently, some owners
of the exotic cars purchased elsewhere also use ECI maintenance service.
ECI
appreciates continuing business of its patrons; consequently, the
company wants to maintain as much information about each valuable
customer as it possibly can.
As
a result, ECI diligently records how much money each customer spends on
buying cars (more than a half have purchased two cars and about third
bought three or more), on maintenance service (how much customer spends
overall, and per car), and whether or not a customer ever traded a car
in.
Course Project Description
Your
goal is to develop a database that shall allow ECI to keep track of its
business and provide the management with the following information
regarding the performance of the business:
HR Management
Complete information about ECI employees, especially salesmen and technicians
Inventory Management
Complete information on the number and types of the cars currently kept in stock
Complete information on the number and types of the cars obtained from each source (factory, private collector, trade-in, etc.)
Maintenance Service Management
Complete information on the number and types of the cars that were serviced.
Complete
information on maintenance record of each serviced car (car
information, type of problem, owner, mechanic who performed the
maintenance, and cost of maintenance)
Customer Service Management
Complete information on every customer including information regarding their:
• Total spending on cars
• Total spending on service maintenance
Accounting
Complete information on spending of every customer
Complete information regarding what was paid to each vendor
Complete information regarding what was received by ECI from the sales of cars
Complete information regarding what was received by ECI from the maintenance service
NOTE:
You don’t have to implement all of the required functionality- use the
description above to determine entities and attributes that you data
model must include.
Submission of the Project
You are going to submit the Course Project over the following sequence of steps (a single deliverable per step):
1. Course Project: ERD
2. Course Project: SQL code used to create your DB Schema and populate your DB Tables
3. Course Project: Reports
Course Project Grading Guidelines
Students, your project is worth 100 points total, with the following allocation of points:
Project Component
|
Points
|
Functioning Database SQL code used to create your DB Schema Contents of the DB Tables (minimum 10 records per table)
|
70
|
Functioning reports
(You must be able to run your reports, based on 10 queries of your
preference. 4 queries must involve at least 3 tables, and another 4
queries must involve at least 4 tables.)
|
30
|
Total points
|
100
|
NOTE Regarding the format of your submissions:
The complete package that you will need to submit must include the following:
1. Copy of your database created using SQL Server
2. A Word document/PDF file including the following parts:
1. ERD of your database
2. SQL code that you used to create your schema (copy & paste from SQL Server)
3. SQL code that you used to populate your tables (copy & paste from SQL Server)
4. Description
of your reports- you must clearly explain what information each of your
reports allows you to obtain (you will need to have 10 descriptions)
No comments:
Post a Comment