Download This article is about microprocessors. For central processing units more generally, see Ce and more Slides Microprocessors in PDF only on Docsity! Green University of Bangladesh Department of Computer Science and Engineering(CSE) Faculty of Science and Engineering Semester: Fall, Year: 2022, B.Sc. in CSE (Day) LAB REPORT NO : 01 Course Title: Database System Lab Course Code: CSE210 Section: 211D2 Lab Experiment Name: Implementation of database update in MySQL Student Details Name ID MD: Al Imran Suvo 203002044 Lab Date : 24-10-2022 Submission Date : 31-10-2022 Course Teacher’s Name : Tanoy Debnath [For Teachers use only: Don’t Write Anything inside this box] Lab Report Status Marks: ………………………………… Signature:..................... Comments:.............................................. Date:.............................. 1. TITLE OF THE LAB EXPERIMENT Implementation of database update in MySQL. Create This following Bank Database. branch (branch_name, branch_city, assets) customer (customer_id,customer_name, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_id, account_number) borrower (customer_id, loan_number) • Tables are placed according to parent and child relationship • Create above table considering PRIMARY KEY and FOREIGN KEY. • Data type for amount and balance are INTEGER otherwise VARCHAR(13). • Insert records into your table. • Add column Email in customer relation and Set the value. • Change the name of column name customer_city and modify the data type of column assets 2. OBJECTIVES i. To learn the advance knowledge for modifying and updating MySQL database. ii. To learn the implementation of different types of modifying statements using ADD, DROP, CHANGE and UPDATE. 3. PROCEDURE and IMPLEMENTATION Table 1: Branch Query CREATE TABLE Branch( branch_name varchar (20) NOT Null UNIQUE , branch_city varchar (20) NOT Null, assets int (100) NOT Null, PRIMARY KEY (branch_name) ); Structure Insertion Table 4: Loan Query CREATE TABLE Loan( Loan_number varchar(15) not null unique, Branch_name varchar(15) not null, Amount int (13) NOT null, PRIMARY KEY(Loan_number) ); Structure Insertion Table 5: Depositor Query CREATE TABLE Depositor( customer_id varchar(20), account_number varchar (15), PRIMARY KEY (customer_id, account_number), FOREIGN KEY (customer_id) REFERENCES customer(customer_id), FOREIGN KEY (account_number) REFERENCES account (account_number) ); O Structure
# Name Type Collation Attributes Null Default Comments Extra Action
1 customer id & varchar(20) utf8mb4_general ci No None GP Change QDrop More
Q 2. account. number pe varchar(15) utf8mb4 general ci No None Gf Change Q Drop More
O Insertion
+T > ‘Y customer_id account_number
OQ Edit }eCopy @ Delete A205001 10268125121
O Edit }e Copy @ Delete A980177 13154686556
ODO Edit }eCopy @ Delete B338077 2156468425
ODO Edit 3c Copy © Delete C550609 35454879831
OQ Edit Fe Copy @ Delete D706602 45469873222
O Check all With selected: gf Edit # Copy @ Delete {5} Export
Query ALTER TABLE customer CHANGE COLUMN Customer_city Address varchar (20) NOT NULL; Table after change the column name ❑ modify the data type of column assets Query ALTER TABLE branch MODIFY assets varchar (25); Table after the change of datatype 4. DISCUSSION During completing this lab report, I’ve learned many things like creating six tables in a database, using ALTER constraint to add a new column to an existing table, rename the column name and change the data type, using of primary key and foreign key. Here I create parents child relationship between the tables. I faced some problem but later that was solved.
