DBMS Lab 15-16: Design and implementation of COMPANY database. Design and implementation of MOVIE DATABASE.

Consider the schema for COMPANY Database:

• EMPLOYEE (SSN, Name, Address, Salary, Sex)

• DEPARTMENT (DNo, DName, MgrSSN)

• PROJECT (PNo, PName, PLocation, DNo)

• WORKS_FOR(SSN,Dno)

• WORKS_ON (SSN, Pno, Hours)

•

SCHEMA DIAGRAM:

ER DIAGRAM:

CREATE ALL TABLES:

CREATE TABLE EMPLOYEE(SSN INT PRIMARY KEY,ENAME VARCHAR(20),DOB DATE,ADDRESS VARCHAR(40),SALARY REAL,SEX ENUM('MALE','FEMALE'));

CREATE TABLE DEPARTMENT(DNO INT PRIMARY KEY, DNAME VARCHAR(30), MGR_SSN INT, FOREIGN KEY(MGR_SSN) REFERENCES EMPLOYEE(SSN));

CREATE TABLE PROJECT(PNO INT PRIMARY KEY, PNAME VARCHAR(40), PLOCATION VARCHAR(30), DNO INT, FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));

CREATE TABLE WORKS_FOR(SSN INT,DNO INT,PRIMARY KEY(SSN,DNO), FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY(DNO) REFERENCES DEPARTMENT(DNO));

CREATE TABLE WORKS_ON(SSN INT, PNO INT, HOURS FLOAT, PRIMARY KEY(SSN,PNO), FOREIGN KEY(SSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY(PNO) REFERENCES PROJECT(PNO));

INSERT INTO TABLES

INSERT INTO EMPLOYEE VALUES(101,’AMIT’, ‘2000-03-30’,’HEBBAL’,40000, ’MALE’);

INSERT INTO DEPARTMENT VALUES(1, ‘RESEARCH’, 103);

INSERT INTO PROJECT VALUES(501,'DATA ANALYTICS','MUMBAI',1);

INSERT INTO WORKS_FOR VALUES(101,1);

INSERT INTO WORKS_ON VALUES(101,501,4);

Write and Execute the SQL Queries for the following statements:

1. Make a list of all project numbers for projects that involve an employee whose last name is ‘Ganesh’, either as a worker or as a manager of the department that controls the project.

(SELECT DISTINCT P.PNO FROM PROJECT P, DEPARTMENT D, EMPLOYEE E WHERE P.DNO=D.DNO AND D.MGR_SSN=E.SSN AND E.ENAME='GANESH')

UNION

(SELECT DISTINCT P.PNO FROM PROJECT P, WORKS_ON W, EMPLOYEE E WHERE P.PNO=W.PNO AND E.SSN=W.SSN AND E.ENAME='GANESH');

2. Show the resulting salaries if every employee working on the ‘IoT’ project is given a 10 percent raise.

SELECT E.ENAME, E.SALARY AS PRESENT_SAL,1.1 * E.SALARY AS INCREASED_SAL FROM EMPLOYEE E, WORKS_ON W, PROJECT P WHERE E.SSN=W.SSN AND W.PNO=P.PNO AND P.PNAME='IOT';

3. Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the maximum salary, the minimum salary, and the average salary in this department

SELECT SUM(E.SALARY), MAX(E.SALARY), MIN(E.SALARY), AVG(E.SALARY) FROM EMPLOYEE E JOIN WORKS_FOR W ON(E.SSN=W.SSN) JOIN DEPARTMENT D ON(W.DNO=D.DNO) WHERE D.DNAME='RESEARCH';

4. For each project on which more than ONE employee(s) work, retrieve the project number, the project name, and the number of employees who work on the project.

SELECT P.PNO, P.PNAME, COUNT(*)

FROM PROJECT P, WORKS_ON W

WHERE W.PNO=P.PNO

GROUP BY P.PNO, P.PNAME

HAVING COUNT(*)>1;

5. For each project, retrieve the project number, the project name, and the number of employees from department 3 who work on the project.

SELECT P.PNO, P.PNAME, COUNT(*)

FROM PROJECT P, WORKS_ON W, EMPLOYEE E

WHERE P.PNO=W.PNO AND W.SSN=E.SSN AND P.DNO=3

GROUP BY P.PNO, P.PNAME;

6. Count the number of Male & Female Employees in the COMPANY.

SELECT COUNT(IF (SEX='MALE',1,NULL)) AS MALE,

COUNT(IF (SEX='FEMALE',1,NULL)) AS FEMALE

FROM EMPLOYEE;

7. Display the count of Male and Female Employees working for Department 2.

SELECT (SELECT COUNT(*) FROM

EMPLOYEE E,WORKS_FOR W

WHERE E.SSN=W.SSN AND W.DNO=2 AND E.SEX='MALE') AS MALE, (SELECT COUNT(*) AS MALE FROM EMPLOYEE E,WORKS_FOR W WHERE E.SSN=W.SSN AND W.DNO=2 AND E.SEX='FEMALE') AS FEMALE;

8. Display the Average working hours for all the projects.

SELECT PNO,AVG(HOURS) AS AVG_TIME

FROM WORKS_ON

GROUP BY PNO

ORDER BY PNO;

9. Find the avg working hours of all female employees.

SELECT (SELECT SUM(HOURS)

FROM EMPLOYEE E, WORKS_ON W

WHERE E.SSN=W.SSN AND E.SEX='FEMALE')/(SELECT COUNT(DISTINCT E.SSN) FROM EMPLOYEE E, WORKS_ON W WHERE E.SSN=W.SSN AND E.SEX='FEMALE') AS AVG_HOURS;

10. Display the list of employees of age above 25.

SELECT ENAME, DOB, TIMESTAMPDIFF(YEAR, DOB, CURDATE()) AS AGE FROM EMPLOYEE

WHERE TIMESTAMPDIFF(YEAR, DOB, CURDATE())>25;

11. Display SSN, Name and Age of Employees working on Project 501.

SELECT SSN, ENAME, TIMESTAMPDIFF(YEAR,DOB,CURDATE()) AS AGE

FROM EMPLOYEE

WHERE SSN IN(SELECT SSN FROM WORKS_ON WHERE PNO=501);

12. Display SSN, Name and Age of Employees NOT working on Project 501.

SELECT SSN, ENAME, TIMESTAMPDIFF(YEAR,DOB,CURDATE()) AS AGE

FROM EMPLOYEE

WHERE SSN NOT IN(SELECT SSN FROM WORKS_ON WHERE PNO=501);

13. Display the Employee name and Address who work for ‘Research’ Department (USING JOINS).

SELECT DISTINCT ENAME, ADDRESS FROM((EMPLOYEE E JOIN WORKS_FOR W ON E.SSN=W.SSN) JOIN DEPARTMENT D ON W.DNO=D.DNO AND D.DNAME='RESEARCH');

Use of Conditional Operators.

SELECT IF(1>3,'TRUE','FALSE');

14. Display SSN, Name and HE/SHE for all employees respectively.

SELECT SSN,ENAME,IF((SELECT SEX)='MALE','HE','SHE') AS GENDER FROM EMPLOYEE;

15. Display SSN, Name and AGE as Major or Minor for all employees.

SELECT SSN,ENAME,IF((SELECT TIMESTAMPDIFF(YEAR,DOB, CURDATE()))>18, 'MAJOR','MINOR') AS AGE FROM EMPLOYEE;

CREATING & USING VIEWS

16. Create Views for FEMALE employees to check Age>22 and Salary>25000

· CREATE VIEW CHECKSAL_F AS SELECT * FROM EMPLOYEE WHERE TIMESTAMPDIFF(YEAR,DOB,CURDATE())>22 AND SEX='FEMALE' AND SALARY>25000;

· SELECT * FROM CHECKSAL_F;

17. Create Views for MALE employees to check Age>22 and Salary>25000

· CREATE VIEW CHECKSAL_M AS SELECT * FROM EMPLOYEE WHERE TIMESTAMPDIFF(YEAR,DOB,CURDATE())>22 AND SEX='MALE' AND SALARY>25000;

· SELECT * FROM CHECKSAL_M;

USE OF TRIGGERS

DELIMITER //

CREATE TRIGGER SALCHECK BEFORE INSERT ON EMPLOYEE FOR EACH ROW IF NEW.SALARY < 0 THEN SET NEW.SALARY =9999; END IF;//

DELIMITER ;

INSERT INTO EMPLOYEE VALUES(200,'NEHA','1995-09-12', 'BELLANDUR',-100,'FEMALE');

SELECT * FROM EMPLOYEE;

Lab 16: Design and implementation of MOVIE DATABASE.

Consider the schema for Movie Database:

ACTOR (Act_id, Act_Name, Act_Gender)

DIRECTOR (Dir_id, Dir_Name, Dir_Phone)

MOVIES (Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id)

MOVIE_CAST (Act_id, Mov_id, Role)

RATING (Mov_id, Rev_Stars)

Write SQL queries to

1. List the titles of all movies directed by ‘Hitchcock’.

2. Find the movie names where one or more actors acted in two or more movies.

3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation).

4. Find the title of movies and number of stars for each movie that has at least one rating and find the highest number of stars that movie received. Sort the result by movie title.

5. Update rating of all movies directed by ‘Steven Spielberg’ to 5.

Solution:

Entity-Relationship Diagram

Table Creation

CREATE TABLE ACTOR (

ACT_ID INTEGER (3),

ACT_NAME VARCHAR (20),

ACT_GENDER CHAR (1),

PRIMARY KEY (ACT_ID));

CREATE TABLE DIRECTOR (

DIR_ID INTEGER (3),

DIR_NAME VARCHAR (20),

DIR_PHONE VARCHAR (10),

PRIMARY KEY (DIR_ID));

CREATE TABLE MOVIES (

MOV_ID INTEGER (4),

MOV_TITLE VARCHAR (25),

MOV_YEAR INTEGER (4),

MOV_LANG VARCHAR (12),

DIR_ID INTEGER (3),

PRIMARY KEY (MOV_ID),

FOREIGN KEY (DIR_ID) REFERENCES DIRECTOR (DIR_ID));

CREATE TABLE MOVIE_CAST (

ACT_ID INTEGER (3),

MOV_ID INTEGER (4),

ROLE VARCHAR (10),

PRIMARY KEY (ACT_ID, MOV_ID),

FOREIGN KEY (ACT_ID) REFERENCES ACTOR (ACT_ID),

FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));

CREATE TABLE RATING (

MOV_ID INTEGER (4),

REV_STARS VARCHAR (25),

PRIMARY KEY (MOV_ID),

FOREIGN KEY (MOV_ID) REFERENCES MOVIES (MOV_ID));

Table Descriptions

mysql> DESC ACTOR;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

| ACT_ID | int(3) | NO | PRI | NULL | |

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> DESC DIRECTOR;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

| DIR_ID | int(3) | NO | PRI | NULL | |

+-----------+-------------+------+-----+---------+-------+

3 rows in set (0.07 sec)

mysql> DESC MOVIE_CAST;

+--------+-------------+------+-----+---------+-------+

+--------+-------------+------+-----+---------+-------+

| ACT_ID | int(3) | NO | PRI | NULL | |

| MOV_ID | int(4) | NO | PRI | NULL | |

+--------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> DESC MOVIES;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

| MOV_ID | int(4) | NO | PRI | NULL | |

| MOV_YEAR | int(4) | YES | | NULL | |

| DIR_ID | int(3) | YES | MUL | NULL | |

+-----------+-------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

mysql> DESC RATING;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

| MOV_ID | int(4) | NO | PRI | NULL | |

+-----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

Insertion of Values to Tables

INSERT INTO ACTOR VALUES (301,'ANUSHKA','F');

INSERT INTO ACTOR VALUES (302,'PRABHAS','M');

INSERT INTO ACTOR VALUES (303,'PUNITH','M');

INSERT INTO ACTOR VALUES (304,'JERMY','M');

INSERT INTO DIRECTOR VALUES (60,'RAJAMOULI', 8751611001);

INSERT INTO DIRECTOR VALUES (61,'HITCHCOCK', 7766138911);

INSERT INTO DIRECTOR VALUES (62,'FARAN', 9986776531);

INSERT INTO DIRECTOR VALUES (63,'STEVEN SPIELBERG', 8989776530);

INSERT INTO MOVIES VALUES (1001,'BAHUBALI-2', 2017, 'TELUGU', 60);

INSERT INTO MOVIES VALUES (1002,'BAHUBALI-1', 2015, 'TELUGU', 60);

INSERT INTO MOVIES VALUES (1003,'AKASH', 2008, 'KANNADA', 61);

INSERT INTO MOVIES VALUES (1004,'WAR HORSE', 2011, 'ENGLISH', 63);

INSERT INTO MOVIE_CAST VALUES (301, 1002, 'HEROINE');

INSERT INTO MOVIE_CAST VALUES (301, 1001, 'HEROINE');

INSERT INTO MOVIE_CAST VALUES (303, 1003, 'HERO');

INSERT INTO MOVIE_CAST VALUES (303, 1002, 'GUEST');

INSERT INTO MOVIE_CAST VALUES (304, 1004, 'HERO');

INSERT INTO RATING VALUES (1001, 4);

INSERT INTO RATING VALUES (1002, 2);

INSERT INTO RATING VALUES (1003, 5);

INSERT INTO RATING VALUES (1004, 4);

Mysql> SELECT * FROM ACTOR;

+--------+----------+------------+

| ACT_ID | ACT_NAME | ACT_GENDER |

+--------+----------+------------+

| 301 | ANUSHKA | F |

| 302 | PRABHAS | M |

| 303 | PUNITH | M |

| 304 | JERMY | M |

+--------+----------+------------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM DIRECTOR;

+--------+------------------+------------+

| DIR_ID | DIR_NAME | DIR_PHONE |

+--------+------------------+------------+

| 60 | RAJAMOULI | 8751611001 |

| 61 | HITCHCOCK | 7766138911 |

| 62 | FARAN | 9986776531 |

| 63 | STEVEN SPIELBERG | 8989776530 |

+--------+------------------+------------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM MOVIES;

+--------+------------+----------+----------+--------+

+--------+------------+----------+----------+--------+

| 1001 | BAHUBALI-2 | 2017 | TELUGU | 60 |

| 1002 | BAHUBALI-1 | 2015 | TELUGU | 60 |

| 1003 | AKASH | 2008 | KANNADA | 61 |

| 1004 | WAR HORSE | 2011 | ENGLISH | 63 |

+--------+------------+----------+----------+--------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM MOVIE_CAST;

+--------+--------+---------+

| ACT_ID | MOV_ID | ROLE |

+--------+--------+---------+

| 301 | 1001 | HEROINE |

| 301 | 1002 | HEROINE |

| 303 | 1002 | GUEST |

| 303 | 1003 | HERO |

| 304 | 1004 | HERO |

+--------+--------+---------+

5 rows in set (0.00 sec)

mysql> SELECT * FROM RATING;

+--------+-----------+

| MOV_ID | REV_STARS |

+--------+-----------+

| 1001 | 4 |

| 1002 | 2 |

| 1003 | 5 |

| 1004 | 4 |

+--------+-----------+

4 rows in set (0.00 sec)

Queries:

1. List the titles of all movies directed by ‘Hitchcock’.

mysql> SELECT MOV_TITLE

-> FROM MOVIES

-> WHERE DIR_ID IN (SELECT DIR_ID

-> FROM DIRECTOR

-> WHERE DIR_NAME = 'HITCHCOCK');

+-----------+

| MOV_TITLE |

+-----------+

| AKASH |

+-----------+

1 row in set (0.00 sec)

2. Find the movie names where one or more actors acted in two or more movies.

mysql> SELECT MOV_TITLE

-> FROM MOVIES M, MOVIE_CAST MV

-> WHERE M.MOV_ID=MV.MOV_ID AND ACT_ID IN (

SELECT ACT_ID

FROM MOVIE_CAST GROUP BY ACT_ID HAVING

COUNT(ACT_ID)>1)

-> GROUP BY MOV_TITLE

-> HAVING COUNT(*)>1;

+------------+

| MOV_TITLE |

+------------+

| BAHUBALI-1 |

+------------+

1 row in set (0.06 sec)

3. List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation).

SELECT ACT_NAME, MOV_TITLE, MOV_YEAR

FROM ACTOR A

JOIN MOVIE_CAST C

ON A.ACT_ID=C.ACT_ID

JOIN MOVIES M

ON C.MOV_ID=M.MOV_ID

WHERE M.MOV_YEAR NOT BETWEEN 2000 AND 2015;

SELECT A.ACT_NAME, C.MOV_TITLE, C.MOV_YEAR FROM ACTOR A, MOVIE_CAST B, MOVIES C

WHERE A.ACT_ID=B.ACT_ID

AND B.MOV_ID=C.MOV_ID

AND C.MOV_YEAR NOT BETWEEN 2000 AND 2015;

+----------+------------+----------+

| ACT_NAME | MOV_TITLE | MOV_YEAR |

+----------+------------+----------+

| ANUSHKA | BAHUBALI-2 | 2017 |

+----------+------------+----------+

1 row in set (0.01 sec)

4. Find the title of movies and number of stars for each movie that has at least one rating and find the highest number of stars that movie received. Sort the result by movie title.

mysql> SELECT MOV_TITLE, MAX(REV_STARS)

-> FROM MOVIES

-> INNER JOIN RATING USING(MOV_ID)

-> GROUP BY MOV_TITLE

-> HAVING MAX(REV_STARS)>0

-> ORDER BY MOV_TITLE;

+------------+----------------+

| MOV_TITLE | MAX(REV_STARS) |

+------------+----------------+

| AKASH | 5 |

| BAHUBALI-1 | 2 |

| BAHUBALI-2 | 4 |

| WAR HORSE | 5 |

+------------+----------------+

4 rows in set (0.23 sec)

5. Update rating of all movies directed by ‘Steven Spielberg’ to 5.

mysql> UPDATE RATING

-> SET REV_STARS=5

-> WHERE MOV_ID IN (SELECT MOV_ID

FROM MOVIES

WHERE DIR_ID IN (SELECT DIR_ID

-> FROM DIRECTOR

-> WHERE DIR_NAME = 'STEVEN SPIELBERG'));

mysql> SELECT * FROM RATING;

+--------+-----------+

| MOV_ID | REV_STARS |

+--------+-----------+

| 1001 | 4 |

| 1002 | 2 |

| 1003 | 5 |

| 1004 | 5 |

+--------+-----------+

4 rows in set (0.00 sec)

Search This Blog

B.TECH Lab Programs

DBMS Lab 15-16: Design and implementation of COMPANY database. Design and implementation of MOVIE DATABASE.

Lab 16: Design and implementation of MOVIE DATABASE.

Comments

Post a Comment

Popular posts from this blog

MICROCONTROLLER AND EMBEDDED SYSTEMS LABORATORY Part A

IoT Lab 8-11: Controlling Intensity of Led using Potentiometer, Traffic Control System, Interfacing of Ultrasonic sensor, IR sensor, Temperature, LDR