Reference no: EM13928492
Assignments 2
1. Items 1A - 1C refer to a database that keeps track of automobile and option sales in a car dealership. CAROPTION refers to some optional equipment installed on an automobile that is sold. You will create the database in Oracle.
The ERD, the conceptual model of the database, contained the following:
- three entities: SALESPERSON, CAR and CAROPTION
- The ternary relationship, among the entities SALESPERSON, CAR and CAROPTION
The relations (tables) of the database are listed below. The primary key of each relation is shown in bold, underline and italics lettering.
CAR(SerialNo, Model, Manufacturer, List_Price)
CAROPTION(OptionNo, OptionName, List_Price)
CARSALE(SalespersonId, SerialNo, Date, Car_Sale_price)
OPTIONSALE(SalespersonId, SerialNo, OptionNo, Option_Sale_price)
SALESPERSON(SalespersonId, Name, Phone)
1A. Give the DDL commands to create the tables, primary keys and referential integrity constraints. The DDL must have at least an example of each of the following. Use comments to indicate the examples.
1. Not Null check constraint
2. Column check constraint
3. Table check constraint
4. Column default value
5. Table-level constraint
6. Check constraint
DDL:
1.B Provide the DML and logs that show that you populated the database with the data shown below.
Car
SerialNo Model Manufacturer List_Price
ABC LX Kia 19,000
BCD EX Kia 19,500
EFG SX Kia 26,000
SALESPERSON
SalespersonId Name Phone
101 John 555-555-5555
102 Paul 555-556-5678
103 Mary 555-678-7890
OPTION
OptionNo OptionName List_Price
1 2.4-liter 2,000
2 2.0-liter turbo 3,000
3 Sun roof 1,000
CARSALE
SalespersonId SerialNo Date Car_Sale_price
101 ABC 10/16/2011 18,500
101 BCD 10/16/2011 21,000
102 EFG 10/16/2011 25,500
OPTIONSALE
SalespersonId SerialNo OptionNo Option_Sale_price
101 ABC 1 1,500
101 ABC 3 1,000
102 EFG 2 2,500
102 EFG 3 750
DML:
1.C. 1. Do a search to find out the name of a salesperson who sold a car on 10/16/2011 for 25,500.
1C.2. Find out what kind of model and manufacturer the car that was sold was.
|
Importance of ethics in the workplace
: Identify and discuss the importance of ethics in the workplace. What do you think the business world would be like without them? Why?
|
|
Ways to change the course of a brand
: One of the ways to change the course of a brand is to modify the product. Under product modification, ________ adds size, weight, materials, supplements, and accessories that expand the product's performance, versatility, safety, or convenience.
|
|
Calculate the amount of gain or loss
: Calculate the amount of gain or loss from the sale of the asset under each of the depreciation methods.
|
|
Strategic planning-decision making
: Explain the basic steps in the planning process. Describe the steps in the decision-making process and predict how the personal attributes of the manager influence decision making.
|
|
Primary keys and referential integrity constraints
: Give the DDL commands to create the tables, primary keys and referential integrity constraints. The DDL must have at least an example of each of the following. Use comments to indicate the examples.
|
|
How a bond can be classified as a fixed-income security
: Explain how a bond can be classified as a fixed-income security when the yield to maturity can fluctuate significantly over time, depending on the market price of the bond.
|
|
Explain how logistics performance crucial to jit philosophy
: How can strategic procurement contribute to the quality of products produced by a manufacturing organization? Explain how logistics performance is crucial to a Just In Time (JIT) philosophy.
|
|
Elements of an organization external environment
: Describe the elements of an organization's external environment. Why is it important to understand the external environment?
|
|
What was the dollar amount of the price change on the bond
: How much would you have to pay for one of these $1,000 face value bonds? What was the dollar amount of the price change on this bond?
|