CellTell, a cellular phone service provider, offers cell phone service contracts to their customers. They have an information system that stores the details about these contracts, selected plans, phones, and accessories. ERD and Schema for the model is: CUSTOMER ( ID, Lname, Fname, Street, City, ZipCode, State, Phone, CreditScore, Credit_Org, CS_Date) PLAN ( Plan_ID, Start_Date, End_Date, BasePrice, Plan_Type) VOICE ( vPlan_ID@, NumMin, HasVoiceMail, HasThreeWay, OverageFee) TEXT ( tPlan_ID@, TextLimit, HasMMS, MmsOverage, TextOverage) DATA ( dPlan_ID@, DataLimit, NetworkTechnology, OverageFee) CONTRACT ( Contract_ID, Customer_ID@, vPlan_ID@, tPlan_ID@, dPlan_ID@, Start_Date, End_Date, DiscountPerc) MANUFACTURER ( Manuf_ID, Manuf_Name, Contact_Lname, Contact_Fname, Contact_Phone, Contact_Email) PRODUCT ( Product_ID, Name, Manuf_ID@, CostPaid, BasePrice, Type) CELLPHONE ( Phone_ID@, Model, NetworkTechnology, OS) ACCESSORY ( Access_ID@, Category) PHONE_ACCESS ( Phone_ID@, Access_ID@) CONTRACT_CELLPHONE ( Contract_ID@, Phone_ID@, Seq#, PaidPrice, ESN) (1) CellTell would like to add website information about the manufacturers of products they sell. Add a new column named “Website” to the manufacturer table. Then, for each manufacturer add their website information (doesn't have to be the actual website of the listed product). (2) Add complete information on two new cell-phones (iPhone 12 Pro & iPhone 12 Mini) and associate each of the new cellphones with two existing accessories (i.e., accessories we already have in the database). Search the Internet to find the information you need on new cell-phones. All the values in each insert statement are to be provided. (3) CellTell wishes to reach out to its high-end customers. So it needs to know ‘active’ customer who purchased a cellphone with a price more than $300. List the customer id, his/her name, the phone they have (i.e., model), price of the phone, and the manufacturer (ID and Name). Order the result by the manufacturer and then by model. (4) Show query result of question #3 (5) List customers (id, first name and last name) who have opted for voice, text, and data in their individual contracts. Include the start and end dates of the contracts. Use a set operation. (6) Show query result of question #5 (7) CellTell wishes to promote its data plan to their active customers. Therefore, they wish to know who has a phone with data capability, but has not signed up for a data plan. List customer ID, name, address, and phone number. (8) Show query result of question #7
CellTell, a cellular phone service provider, offers cell phone service contracts to their customers. They have an
CUSTOMER ( ID, Lname, Fname, Street, City, ZipCode, State, Phone, CreditScore, Credit_Org, CS_Date)
PLAN ( Plan_ID, Start_Date, End_Date, BasePrice, Plan_Type)
VOICE ( vPlan_ID@, NumMin, HasVoiceMail, HasThreeWay, OverageFee)
TEXT ( tPlan_ID@, TextLimit, HasMMS, MmsOverage, TextOverage)
DATA ( dPlan_ID@, DataLimit, NetworkTechnology, OverageFee)
CONTRACT ( Contract_ID, Customer_ID@, vPlan_ID@, tPlan_ID@, dPlan_ID@, Start_Date, End_Date, DiscountPerc)
MANUFACTURER ( Manuf_ID, Manuf_Name, Contact_Lname, Contact_Fname, Contact_Phone, Contact_Email)
PRODUCT ( Product_ID, Name, Manuf_ID@, CostPaid, BasePrice, Type)
CELLPHONE ( Phone_ID@, Model, NetworkTechnology, OS)
ACCESSORY ( Access_ID@, Category)
PHONE_ACCESS ( Phone_ID@, Access_ID@)
CONTRACT_CELLPHONE ( Contract_ID@, Phone_ID@, Seq#, PaidPrice, ESN)
(1) CellTell would like to add website information about the manufacturers of products they sell. Add a new column named “Website” to the manufacturer table. Then, for each manufacturer add their website information (doesn't have to be the actual website of the listed product).
(2) Add complete information on two new cell-phones (iPhone 12 Pro & iPhone 12 Mini) and associate each of the new cellphones with two existing accessories (i.e., accessories we already have in the
(3) CellTell wishes to reach out to its high-end customers. So it needs to know ‘active’ customer who purchased a cellphone with a price more than $300. List the customer id, his/her name, the phone they have (i.e., model), price of the phone, and the manufacturer (ID and Name). Order the result by the manufacturer and then by model.
(4) Show query result of question #3
(5) List customers (id, first name and last name) who have opted for voice, text, and data in their individual contracts. Include the start and end dates of the contracts. Use a set operation.
(6) Show query result of question #5
(7) CellTell wishes to promote its data plan to their active customers. Therefore, they wish to know who has a phone with data capability, but has not signed up for a data plan. List customer ID, name, address, and phone number.
(8) Show query result of question #7
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
