13. Making tServiceProvided and tTechCost available to a new query and inserting

Question

13. Making tServiceProvided and tTechCost available to a new query and inserting the fields TechLevel, MachineID, and Minutes results in a query with the following number of tows: a number of rows of tServicesProvided b. number of rows of tTechCost c. number of rows of tServiceProvided plus number of rows of tTechCost d. number of rows of tServiceProvided times the mamber of rows of tTechCost e. none of the above Problems 1) Tables/Normalization (14 points) You are building a database for a shoe retailer in Corvallis. Create the database tables needed to store information about the following attributes: customer name, customer address, customer telephone, names of customers children, salesperson name, salesperson address, sales department (e-g, used ears, new cars, or leases), sales department phone, department in which a salesperson works (assume that each salesperson works in only one department), shoe model, shoe color, shoe sales price, sales date, shoes purchased by each customer. Clearly indicate all primary and foreign keys in your tables. You may need to crcale some primary keys 2) Internal Control Weaknesses (12 points) For each the scenarios described below, identify 2 control procedures that could have prevented problem, and explain why each of the controls would have helped a) You manage a sales firm, and you give laptops to all salespeople. Salespeople can input orders and submit them electronically while on the road. You have had a few problems wit this system. In one instance, and order for 5 PC's was input by the salesperson as 500 Pc another case, the salesperson failed to input a delivery addes

Explanation / Answer

Its not mentioned here weather you need mySQL queries for the table or just the table headers, so I am providing the SQL statement to generate each table. If you need this in some other form of representation, then post in the comments below. Though you should easily be able to convert the answer to whichever form you like on your own after reading the SQL statements.

CREATE TABLE CUSTOMER(
CUSTOMER_NAME varchar(50) NOT NULL PRIMARY KEY,
CUSTOMER_ADDRESS varchar(100),
CUSTOMER_PHONE_NUMBER int,
CUSTOMER_CHILDREN_NAME varchar(50),
SHOE_MODEL_PURCHASED int FOREIGN KEY REFRENCES SHOE(SHOE_MODEL)
);

CREATE TABLE SALES_PERSON(
SALES_PERSON_NAME varchar(50) NOT NULL PRIMARY KEY,
SALES_PERSON_ADDRESS varchar(100),
DEPARTMENT_NAME varchar(10) FOREIGN KEY REFERENCES DEPARTMENT(DEPARTMENT_NAME)
);

CREATE TABLE DEPARTMENT(
DEPARTMENT_NAME varchar(10) NOT NULL PRIMARY KEY,
DEPARTMENT_PHONE_NUMBER int
);

CREATE TABLE SHOE(
SHOE_MODEL int NOT NULL PRIMARY KEY,
SHOE_COLOR varchar(10),
);

CREATE TABLE SALE(
SHOE_MODEL int FOREIGN KEY REFERENCES SHOE(SHOE_MODEL),
SALES_PRICE int,
SALES_DATE date,
PURCHASED_BY char(50) FOREIGN KEY REFERENCES CUSTOMER(CUSTOMER_NAME)
);

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.