-- Create database named "universal"
CREATE DATABASE [universal]
-- Use the "universal" database
USE [universal]
-- Create student table
CREATE TABLE [student] (
s_id INT PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
lname VARCHAR(30) NOT NULL,
s_tell_phone INT,
email VARCHAR(50) UNIQUE
-- Create staff table
CREATE TABLE [staff] (
staff_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
-- Create books table
CREATE TABLE [books] (
ISBN BIGINT PRIMARY KEY,
autho_id INT,
title VARCHAR(50) NOT NULL,
category VARCHAR(50),
edition VARCHAR(50),
price FLOAT,
publisher_id INT
)
-- Create publisher table
CREATE TABLE [publisher] (
publisher_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
years_of_publisher INT
-- Create tracks table
CREATE TABLE [tracks] (
s_id INT,
staff_id INT,
PRIMARY KEY (s_id, staff_id),
FOREIGN KEY (s_id) REFERENCES [student](s_id),
FOREIGN KEY (staff_id) REFERENCES [staff](staff_id)
-- Create issue table
CREATE TABLE [issue] (
s_id INT,
ISBN BIGINT,
issue_date DATE,
return_date DATE,
PRIMARY KEY (s_id, ISBN),
FOREIGN KEY (s_id) REFERENCES [student](s_id),
FOREIGN KEY (ISBN) REFERENCES [books](ISBN)
-- Create publishes table
CREATE TABLE [publishes] (
ISBN BIGINT,
publisher_id INT,
PRIMARY KEY (ISBN, publisher_id),
FOREIGN KEY (ISBN) REFERENCES [books](ISBN),
FOREIGN KEY (publisher_id) REFERENCES [publisher](publisher_id)
-- Create maintain table
CREATE TABLE [maintain] (
staff_id INT,
ISBN BIGINT,
PRIMARY KEY (staff_id, ISBN),
FOREIGN KEY (staff_id) REFERENCES [staff](staff_id),
FOREIGN KEY (ISBN) REFERENCES [books](ISBN)
-- Insert values into student table
INSERT INTO [student] (s_id, fname, lname, s_tell_phone, email)
VALUES
(1, 'Dessie', 'Tegegne', 935244056, 'dessietegegne@[Link]'),
(2, 'Miki', 'Belay', 945244056, 'floatgne@[Link]'),
(3, 'Getnet', 'Mihret', 96944089, 'keketegegne@[Link]'),
(4, 'Melat', 'Abebe', 96988882056, 'amejegne@[Link]'),
(5, 'Kebede', 'Ayele', 9869244056, 'kidsegegne@[Link]'),
(6, 'Abreham', 'Tsegaye', 935244056, 'alemegegne@[Link]'),
(7, 'Ethiopia', 'Desalegn', 935244056, 'mikiegegneIN@[Link]'),
(8, 'Zemenu', 'Tegege', 935244056, 'zemgegne@[Link]'),
(9, 'Kedur', 'Kiki', 935246856, 'tegegne@[Link]'),
(10, 'Helen', 'Babie', 9358864056, 'dessieteghggegne@[Link]')
-- Update student table
UPDATE [student]
SET fname = 'Kidist'
WHERE s_id = 10
-- Insert values into staff table
INSERT INTO [staff] (staff_id, name)
VALUES
(1, 'Wondosen'),
(2, 'Abbibo')
-- Insert values into books table
INSERT INTO [books] (ISBN, autho_id, title, category, edition, price, publisher_id)
VALUES
(47855452, 2, 'Oromayee', 'Fiction', '2nd', 123, 1),
(896742, 8, 'Extreme Bio', 'Education', '4th', 200, 2)
-- Insert values into publisher table
INSERT INTO [publisher] (publisher_id, name, years_of_publisher)
VALUES
(78, 'Asternega', 2003),
(669, 'Kuraz House', 2013)
-- Insert values into tracks table
INSERT INTO [tracks] (s_id, staff_id)
VALUES (1, 2)
-- Insert values into issue table
INSERT INTO [issue] (s_id, ISBN, issue_date, return_date)
VALUES (3, 47855452, '2000-06-12', '2020-01-25')
-- Insert values into publishes table
INSERT INTO [publishes] (ISBN, publisher_id)
VALUES (47855452, 669)
-- Insert values into maintain table
INSERT INTO [maintain] (staff_id, ISBN)
VALUES (2, 47855452)
1. SQL code to retrieve all the students in the student table:
SELECT * FROM student
A. Relational algebra expression for retrieving all students:
π(*)(student)
2. SQL code to retrieve a student with an id value of 10:
SELECT * FROM student WHERE id = 10
A. Relational algebra expression for retrieving a student with an id value of 10:
σ(id=10)(student)
3. SQL code to retrieve students' Fname, Email, and Id:
SELECT Fname, Email, Id FROM student
A. Relational algebra expression for retrieving students' Fname, Email, and Id:
π(Fname, Email, Id)(student)
4. SQL code to retrieve students' Fname, Email, and Id with an Id value of 10 and student
name is "kidst":
SELECT Fname, Email, Id FROM student WHERE Id = 10 AND Fname = 'kidst'
A. Relational algebra expression for retrieving students' Fname, Email, and Id with an Id value of 10 and
student name is "kidst":
π(Fname, Email, Id)(σ(Id=10 AND Fname='kidst')(student))
5. SQL code to retrieve S_ID, Fname, Email, and PhoneNo of a student:
SELECT S_ID, Fname, Email, PhoneNo FROM student
A. Relational algebra expression for retrieving S_ID, Fname, Email, and PhoneNo of a student:
π(S_ID, Fname, Email, PhoneNo)(student)
6. SQL code to fetch S_ID, Fname, ISBN, Title, Publisher, IssueDate, and ReturnDate:
SELECT S_ID, Fname, ISBN, Title, Publisher, IssueDate, ReturnDate FROM student JOIN books ON
student.S_ID = books.S_ID
A. Relational algebra expression for fetching S_ID, Fname, ISBN, Title, Publisher, IssueDate, and
ReturnDate:
π(S_ID, Fname, ISBN, Title, Publisher, IssueDate, ReturnDate)(student ⨝ S_ID=books.S_ID books)
B. SQL code to create a view for the above query:
CREATE VIEW student_books_view AS
SELECT S_ID, Fname, ISBN, Title, Publisher, IssueDate, ReturnDate FROM student JOIN books ON
student.S_ID = books.S_ID
C. SQL code to alter the created view by including book price in the display list:
ALTER VIEW student_books_view AS
SELECT S_ID, Fname, ISBN, Title, Publisher, IssueDate, ReturnDate, Price FROM student JOIN books ON
student.S_ID = books.S_ID
D. SQL code to drop the altered view:
DROP VIEW student_books_view
7. SQL code to create an index on fname of the student table:
CREATE INDEX idx_fname ON student (fname)
8. SQL code to drop a table (syntax; do not execute):
DROP TABLE table_name
9. SQL code to drop the whole database (syntax; do not execute):
DROP DATABASE database_name
10. SQL code to backup the created database on your local machine:
The process of backing up a database may vary depending on the specific DBMS being used. Here's an
example for MySQL:
mysqldump -u your_username -p your_database_name > /path/to/backup_file.sql
11. SQL code to specify the size of your database:
The exact syntax for retrieving the size of a database may vary depending on the DBMS being used.
SELECT table_schema AS database_name,
SUM(data_length + index_length) AS database_size_bytes,
ROUND(SUM(data_length + index_length) / (1024*1024), 2) AS database_size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
GROUP BY table_schema
GROUP MEMBERS ID
1. Dessie Tegegne ---------------- BDU1411401
2 Hilemikael Belay -------------- BDU1411308
[Link] yihenew --------------BDU1411392
[Link] Tamru ---------------------- BDU1411263
[Link] Mekonnen ------- BDU1411296
[Link] Mulugeta ---------------- BDU1411251
[Link] Gashawu ------------- BDU1411268
[Link] Andualem ------------------BDU1411130
[Link] Abebaw ------------------BDU1411387
[Link] Fisha ------------------ BDU1411313
