1. Imagine that you have been hired to work as a database designer in an electri

Question

1. Imagine that you have been hired to work as a database designer in an electricity company. Your manager asks you to design a database for the billing system. Billing system is responsible to store data for following entities: Branches, Customers, Billing transactions.

Define tables related to the entities above-mentioned and the attributes for each table? Create those tables with primary keys using Create SQL Statements? (10 points)

       b. Insert 5 records in each table with Insert Statements? (5 points)

2. Explain, why could the following statements lead to problem: (25 points)

a. Select Student_name from Student A Where Stud_id=5 Where Age=20;

b. Select All from Student;

c. Select *, [student name] where Stud_id=1

d. Select * from Student order by stud_id where student_name=’Frank’;

e. Select from Student order by stud_id where student_name=Frank;

Explanation / Answer

CREATE TABLE Branches (

Bid int NOT NULL,

BranchName Varchar(200) NOT NULL,

PRIMARY KEY (Bid)

);

CREATE TABLE Customers(

Cid int NOT NULL,

CustomerName Varchar(200) NOT NULL,

PRIMARY KEY (Cid)

);

CREATE TABLE Billing(

Billid int NOT NULL,

Bid int NOT NULL,

Cid int NOT NULL,

FOREIGN KEY(Bid) REFERENCES Branches(Bid),

FOREIGN KEY(Cid) REFERENCES Customers(Cid)

);

2.
a.) AND has to be present between the conditions
Select Student_name from Student A Where Stud_id=5 AND Age=20;

b.) * should be used not All
Select * from Student;

c.) No need to again use student name when used *
Select * where Stud_id=1

d.) ORDER BY should be present at the end
Select * from Student where student_name=’Frank’ order by stud_id;

e.) ORDER BY should be present at the end and String should be in quotations
Select from Student order by stud_id where student_name='Frank';

**Comment for any further queries. Upvote if the answer is satisfactory.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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