HI, I am in jeporady of failing my course I have been fighting fires is Utah and have not had time to complete this project and I do not know where

HI, I am in jeporady of failing my course I have been fighting fires is Utah and have not had time to complete this  project and I do not know where to start if anyone can help me I would be grateful.  this is due before the end of the week.

Goal: The goal of this project is to provide a realistic experience in the conceptual

design, logical design, implementation, operation, and maintenance of a relational database

and associated applications. First, I shall describe the application, then the categories of

requirements, and then some suggestions on how deeply you need to go in each category. A

real project of this sort would require a substantial development team working for several

months (or more). You will complete alone over several weeks. I have chosen to go with

individual rather than group projects because the goal of this project is for you to gain a

personal appreciation of the depth and breadth of issues that go into the design of a database

application, rather than to have you specialize in just one aspect (and rely on others for the

rest).

The project can go well beyond the minimal requirements I outline at the end. I encourage

such extensions. They could turn into a senior design project or other independent work.

Application description: The application is an automobile company, such as General

Motors, Ford, Toyota, or Volkswagen (or maybe a company from yesteryear like Studebaker,

Hudson, Nash, or Packard).

In our hypothetical company, it has been decided to redesign a major part of the database

that underlies company operations. Unfortunately, the manager assigned to solicit database

design proposals are not very computer literate and is unable to provide a very detailed

specification at the technical level. Fortunately, you are able to do that.

The company needs to keep quite a bit of data, but we shall focus on the following aspects

of corporate operations.

• Vehicles. Each vehicle as a vehicle identification number (VIN). Lots of stuff is encoded in real VINs (they are well described on Wikipedia), but you can just make them up if you want.

• Brands: Each company may have several brands (for example, GM has Chevrolet, Pontiac, Buick, Cadillac, GMC, Saturn, Hummer, Saab, Daewoo, Holden, Vauxhall, and Opel and Volkswagen has Volkswagen, Audi, Lamborghini, Bentley, Bugatti, Skoda, and SEAT)

• Models: Each brand offers several models (for example, Buick's models are the Enclave, LaCrosse, and Lucerne, and Mercury's models are the Mariner, Milan, Sable, and Grand Marquis). Each model may come in a variety of body styles (4-door, wagon, etc.)

• Options: We'll stick to color, and maybe engine and transmission.

• Dealers and customers: dealers buy vehicles from the manufacturer and sell them to customers. We'll keep track of sales by date, brand, model, and color; and also, by dealer. This will allow us to use SQL's OLAP tools. Note that a dealer may not sell some of the car company's brands. Dealer's keep some cars in inventory. Some of course, are already sold, but the dealer still keeps track of that fact.

• Suppliers: suppliers supply certain parts for certain models

• Company-owned manufacturing plants: Some plants supply certain parts for certain models; others do final assembly of actual cars.

• Customers: In reality, lots of demographic data are gathered. We'll stick to name, address, phone, gender, and annual income for individual buyers. The customer may also be a company (e.g. Hertz, Avis, or other companies that maintain corporate fleets, but we'll skip that).

We'll skip data on corporate finance, pending bailouts, bankruptcy status etc. Not that

these data are unimportant, but we need to keep the project within bounds.

Data Generation: For simplicity, I will not require realistic data. You can just create

some names or get real ones from the car company web site.

There are many different vehicles, grouped into a variety of (possibly overlapping) categories.

If you get realistic here, things get to be interesting. Thanks to "badge engineering"

many vehicles are the same except for name. That means they can be built in the same

plants from the same parts from the same suppliers. The ISA relationship will get heavy use

here.

Client Requests: You are responsible (for your final turn in) to turn in the following items.

1. E-R Model

a. Construct an E-R diagram representing the conceptual design of the database.

b. Be sure to identify primary keys, relationship cardinalities, etc.

2. Relational Model

a. After creating an initial relational design from your E-R design, refine it based on the principles of relational design (Chapter 7).

b. Create the relations in Oracle database you used for earlier projects.

c. Create indices and constraints as appropriate.

d. If as you refine your design, you discover flaws in the E-R design, go back and change it (even if the earlier design passed the checkpoint.) Your final E-R design must be consistent with your relational design.

3. Populate Relations

a. Include enough data to make answers to your queries interesting and nontrivial for test purposes.

b. You may find it helpful to write a program to generate test data.

4. Queries: You should run several test queries to see that you have loaded your database in the way you intended. The queries listed below are those that your client (the manager from the package delivery company) wants turned in. They may provide further hints about database design, so think about them at the outset of the project.

a. Show sales trends for various brands over the past 3 years, by year, month, week. Then break these data out by gender of the buyer and then by income range.

b. Suppose that it is found that transmissions made by supplier Getrag between two given dates are defective. Find the VIN of each car containing such a transmission and the customer to which it was sold. If your design allows, suppose the defective transmissions all come from only one of Getrag's plants.

c. Find the top 2 brands by dollar-amount sold in the past year.

d. Find the top 2 brands by unit sales in the past year.

e. In what month(s) do convertibles sell best?

f. Find those dealers who keep a vehicle in inventory for the longest average time.

5. Interfaces: There is no requirement to build an interface using other languages or programming environments, unless you would like to. You may use the normal Oracle GUI to access the data, as long as you save the results of your queries (see above).

The final version of the project is to be turned in as a single zip file on blackboard. I will

accept scans for the ER diagram since we are not covering drawing tools for these diagrams,

but Powerpoint does work fairly well for this purpose. Save any scans as PDF files.

1. E-R diagram, plus any explanatory notes. At minimum you must include all the entity and relationship sets implied by this handout. You may go beyond the minimum. Remember that the manager who defined the specifications is not computer literate so the specifications should not be viewed as necessarily being precise and complete.

2. Relational schema. It is likely for many of you that your ER design will be sufficiently extensive that we agree that only a part of the resulting relational design will actually be implemented under Oracle on Edgar3. This is something on which we'll agree before Checkpoint 2. This is the point where we cut implementation effort and data-entry time to something realistic for the course time frame.

3. A set of SQL commands to build the tables, relations, and populate the data for your database.

4. Do NOT turn in a listing of all your data. I can query them if I find it necessary.

5. A set of sample queries.

6. A README file in the top-level folder that explains what is where, etc. Include usage instructions for the interfaces.

7. Everything should be in a single zip file so that when I unzip it, I can read the README

file, follow the directions, and run your project.