The IT service of a telecommunication company has designed the following databas

Question

The IT service of a telecommunication company has designed the following database schema to manage the information of customers, contracts, and payment.

• Customer(CustomerNo, Name, Email, PhoneNo). Customer contains the customer information with a primary key {CustomerNo}. The value of CustomerNo is an integer and the data type of other attributes is a string. {PhoneNo} is a candidate key for Customer. Every customer must have an Email address that contains the character '@'.

• Contract(Service, CustomerNo, MonthlyCost). Contract contains monthly contract information with a primary key {Service, CustomerNo}. The values of CustomerNo and MonthlyCost are integers and the value for Service must be one of the following two strings: 'Internet' or 'Phone'. The default value for MonthlyCost is 50. Make sure that the values of CustomerNo in Contract are valid, i.e., each of them must be a CustomerNo value in Customer. • Payment(CustomerNo, PaymentDate, Amount). Payment contains payment information of customers. The values of CustomerNo and Amount are integers, and the value of PaymentDate is a date. The Amount value must be larger than zero. Make sure that the values of CustomerNo in Payment are valid, i.e., each of them must be a CustomerNo value in Customer. Your task is to add the SQL statements into the template file mydatabase.sql to create the above database schema for the IT Service. Note that, do not use any ALTER TABLE statements and do not change or remove any existing SQL statements that were written in the template file.

Explanation / Answer

The statement for the following are

Create table Customer(CustomerNo int,Name varchar2(20),Email varchar2(30),PhoneNo int,PRIMARY KEY (CustomerNo));

Create table Contract(Service varchar2(10), CustomerNo int,MonthlyCost int DEFAULT 50,FOREIGN KEY (CustomerNo) REFERENCES Customer(CustomerNo),CONSTRAINT Chk_Service CHECK (Service='Internet' OR Service='Phone'),PRIMARY KEY(Service, CustomerNo));

Create table Payment(CustomerNo int,PaymentDate Date, Amount int,FOREIGN KEY (CustomerNo) REFERENCES Customer(CustomerNo),CONSTRAINT Chk_Amount CHECK (Amount > 0));

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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