Question

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. 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.

5. 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.

Note: This uses SQL language (Oracle SQL)