Scribd
Upload
24 views2 pages

Cs 403

The document contains an assignment focused on database normalization and SQL commands. It includes a conversion of a non-1st Normal Form table into 1st Normal Form for a 'Technician' table and provides SQL commands for various database operations such as creating a table, inserting records, deleting records, updating records, and retrieving data. Each SQL command is aligned with specific statements related to database management tasks.

Uploaded by

sevdaism789
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
24 views2 pages

Cs 403

The document contains an assignment focused on database normalization and SQL commands. It includes a conversion of a non-1st Normal Form table into 1st Normal Form for a 'Technician' table and provides SQL commands for various database operations such as creating a table, inserting records, deleting records, updating records, and retrieving data. Each SQL command is aligned with specific statements related to database management tasks.

Uploaded by

sevdaism789
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Student ID: BC220416477

CS403
Assignment no: 02
Question no: 01
The following relation TECHNICIAN is not in 1st Normal Form, you need to convert it
into 1st Normal form.
Answer:
1st Normal form of Technician Table:
Tec_ID Tec_Name Tec_Phone Tec_City
1 Ahmad 03001122334 Multan
1 Ahmad 03021122335 Multan
2 Faisal 03000102034 Karachi
3 Nouman 03211234567 Lahore
3 Nouman 03013216547 Lahore
4 Usman 03133214567 Islamabad

-----------------------------------------------------------------
Question no: 02
You are required to write the SQL commands for each of the following statements in
the given table. Write SQL Commands in the 2nd Column of the following table.
Answer:
Statement SQL Command
1. Create a table named Technician. You CREATE TABLE Technician(
also have to define a primary key and any
foreign key relationships if applicable. TechnicianID INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
E-mail VARCHAR(100)UNIQUE NOT NULL,
PhoneNumber VARCHAR(15),
ManagerID INT,
FOREIGN KEY (Manager ID) REFERENCES,
Manager(ManagerID)
);
2. Insert a new record into the "Customers" INSERT INTO Customers
table with the following details: Customer
ID = "C001", Name = "John Doe", Email ((CustomerID , Name ,Email , Phone , Address)
= "john.doe@example.com", Phone = VALUES (‘001’ , ‘John Doe‘ , ‘john.doe@example.com’ ,
"123-456-7890", and Address = "123 Elm ‘123-456-7890’ , ‘123 Elm Street , Springfield’);
Street, Springfield".

3. Delete all records from the Orders table DELETE FROM Orders
where the Order Date is older than
January 1, 2020. WHERE OrderDate < ’2002-01-01’;
4. Update the "Email" of a customer in the UPDATE Customers
"Customers" table with "Customer ID" =
'C002' to 'new.email@example.com'. SET Email = ‘new.email@example.com’
WHERE CustomerID = ‘C002’;
5. Retrieve the "Name" and "Price" of all SELECT Name , Price
gadgets in the "Gadgets" table where the
"Price" is greater than 5000. FROM Gadgets
WHERE Price > 5000;

----------------------------------------------------------------------

You might also like