Database Normalization and Queries


Added on  2019-09-26

10 Pages1933 Words432 Views
Fundamentals of Database System1.A.The given entity relationship diagram we have four entities Customer, Branch, Transaction, and Account. One Customer has many accounts, one branch has multiple accounts, and one account can perform multiple transactions. A customer have attribute like unique customer identification number (cid), customer name (cname). A branch has attributes like unique branch code (bcode), branch name (bname). Account has attributes like unique account number (ano) account type (atype), account balance (balance), Transaction have unique transaction identification number (tid), transaction date (tdate), transaction type (ttype), transaction amount (tamount).B.Create database OrientalBank;Use OrientalBank;Create table customer(cid varchar(5) primary key,cname varchar(20));Create table Branch (Bcode varchar(5) primary key,BName varchar(20));Create table Account (Ano varchar(5) primary key,Atype varchar(20),Balance integer,Bcode varchar(5)FOREIGN KEY REFERENCES Branch(Bcode),cid varchar(5)FOREIGN KEY REFERENCES Customer(cid));Create table Transection(Tid varchar(5) primary key,Ttype varchar(20),Tamount integer,
Database Normalization and Queries_1

Fundamentals of Database SystemTDate Date,Ano varchar(5)FOREIGN KEY REFERENCES Account(Ano));C.Insert into Customer(cid,cname) values ('c01','Robert'),('c02','Mishell'),('c03','Games'),('c04','Ohio'), ('c05','Gambler'),('c06','Tim'),('c07','Polard'),('c08','Bush'),('c09','Russel'),('c010','Peter'),('c011','Carol'); Insert into Branch (Bcode,Bname) values ('b01','Melbourne city”'),('b02','NSW'),('b03','Oera House'),('b04','Church Road'),
Database Normalization and Queries_2

Fundamentals of Database System('b05','Sydney'),('b06','Walington'),('b07','Baif Road'),('b08','Poland'),('b09','Cichago'),('b010','South Wales');Insert into Account(Ano,Atype,Balance,Bcode,cid) values ('a01','Saving', 123456,'b01','c01'),('a02','Current', 356,'b01','c01'),('a03','Current', 4789,'b02','c02'),('a04','Saving', 555,'b03','c03'),('a05','Saving', 10006,'b05','c04'),('a06','Saving', 56787,'b03','c05'),('a07','Current', 5555,'b06','c05'),('a08','Saving', 234,'b08','c06'),('a09','Current', 234,'b01','c01'),('a010','Saving', 3689,'b09','c04');
Database Normalization and Queries_3

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Database Systems Concepts and Design

Database Operations and Normalization

Database Design and Use