DBMS Experiment 2

Question:



1. Define Database and Database Management System

  • Database: A structured collection of related data stored electronically.

  • Database Management System (DBMS): Software that manages databases, allowing users to create, read, update, and delete data efficiently.

2. Describe primary key.

  • A primary key is a unique attribute or a set of attributes in a table that uniquely identifies each record.

3. Draw E-R diagram for Library Management System.

  • Entities: Book, Member, Librarian, Loan
    Relationships:

  • Member borrows Book

  • Librarian manages Loan

  • Book belongs to Category
    Attributes: Book (BookID, Title, Author), Member (MemberID, Name), Loan (LoanID, DateIssued, DateReturned)

4. Normalize the following table of EMP to 3NF
EMP(empno, ename, mgr, job, deptno, loc, dname)

Step-wise normalization:

  • 1NF: Table already in 1NF (atomic attributes).

  • 2NF: Remove partial dependencies on primary key (empno). Dept info depends on deptno, not empno.
    Split into:

    • EMP(empno, ename, mgr, job, deptno)

    • DEPT(deptno, dname, loc)

  • 3NF: Remove transitive dependency (loc depends on deptno via DEPT, so no change needed here).

Final 3NF tables:

  • EMP(empno, ename, mgr, job, deptno)

  • DEPT(deptno, dname, loc)




Solution:

for MySQL:

1. Creating database named EMP:

CREATE DATABASE EMP;

2. Creating the EMP table:

In MySQL, the closest equivalents would be:

  • Use INT or SMALLINT for numbers.

  • Use VARCHAR(n) for variable-length strings.

  • Use DECIMAL for numbers with decimals.

  • Use DATE for dates.

MySQL version:

CREATE TABLE EMP (
  empno   INT(4),
  ename   VARCHAR(10),
  job     VARCHAR(9),
  mgr     INT(4),
  hiredate DATE,
  sal     DECIMAL(7,2),
  comm    DECIMAL(7,2),
  deptno  INT(2)
);

Note:

  • INT(4) means display width; actual storage size depends on the INT type. For small numbers, consider SMALLINT.

  • For mgr, comm, and deptno—if you expect these to be nullable, add NULL or leave default.

3. Setting empno as the primary key:

ALTER TABLE EMP ADD CONSTRAINT emp_pk PRIMARY KEY (empno);

4. Creating the DEPT table:

CREATE TABLE DEPT (

  deptno INT(2),
  dname VARCHAR(10),
  loc   VARCHAR(20)
);

5. Inserting data:

In MySQL, date literals should be fine as 'YYYY-MM-DD'.

Final MySQL script:

CREATE DATABASE EMP;
USE EMP;

CREATE TABLE EMP (
  empno   INT,
  ename   VARCHAR(10),
  job     VARCHAR(9),
  mgr     INT,
  hiredate DATE,
  sal     DECIMAL(7,2),
  comm    DECIMAL(7,2),
  deptno  INT
);

ALTER TABLE EMP ADD CONSTRAINT emp_pk PRIMARY KEY (empno);

CREATE TABLE DEPT (
  deptno INT,
  dname  VARCHAR(10),
  loc    VARCHAR(20)
);

INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(1001, 'Amit',     'Manager',   NULL,      '2020-01-15', 85000.00, 5000.00, 10),
(1002, 'Priya',    'Analyst',   1001,      '2021-03-20', 60000.00, NULL,    20),
(1003, 'Raj',      'Clerk',     1002,      '2022-07-10', 30000.00, NULL,    10),
(1004, 'Sneha',    'Salesman',  1001,      '2023-01-05', 40000.00, 2000.00, 30),
(1005, 'Karthik',  'Developer', 1002,      '2021-11-25', 55000.00, NULL,    20);

INSERT INTO DEPT (deptno, dname, loc) VALUES
(10, 'Accounts',    'Mumbai'),
(20, 'IT',          'Bangalore'),
(30, 'Sales',       'Delhi'),
(40, 'HR',          'Chennai'),
(50, 'Support',     'Hyderabad');





If you're using SQLite:



1. How do you create a new SQLite database named EMP?


Answer:

In SQLite, a new database is created by connecting to it:


sqlite3 EMP.db



This creates EMP.db if it doesn't already exist.




2. How do you create the EMP table with the specified structure?


Answer:

SQLite-compatible CREATE TABLE with appropriate data types:


CREATE TABLE IF NOT EXISTS EMP (
  empno     INTEGER,
  ename     TEXT,
  job       TEXT,
  mgr       INTEGER,
  hiredate  TEXT,           -- Dates are stored as TEXT in SQLite
  sal       REAL,
  comm      REAL,
  deptno    INTEGER
);





3. How do you assign the empno column as the primary key in the EMP table?


Answer:

In SQLite, primary keys must be defined during table creation.

You must drop and recreate the table to assign a primary key.


DROP TABLE IF EXISTS EMP;

CREATE TABLE EMP (
  empno     INTEGER PRIMARY KEY,
  ename     TEXT,
  job       TEXT,
  mgr       INTEGER,
  hiredate  TEXT,
  sal       REAL,
  comm      REAL,
  deptno    INTEGER
);





4. How do you create the DEPT table with the specified structure?


Answer:


CREATE TABLE IF NOT EXISTS DEPT (
  deptno  INTEGER,
  dname   TEXT,
  loc     TEXT
);



SQLite does not enforce character limits on TEXT, but you can add comments or application-level checks if needed.






1. How do you insert 5 sample rows into the EMP table in SQLite?


Answer:


INSERT INTO EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(1001, 'Amit',     'Manager',   NULL,      '2020-01-15', 85000.00, 5000.00, 10),
(1002, 'Priya',    'Analyst',   1001,      '2021-03-20', 60000.00, NULL,    20),
(1003, 'Raj',      'Clerk',     1002,      '2022-07-10', 30000.00, NULL,    10),
(1004, 'Sneha',    'Salesman',  1001,      '2023-01-05', 40000.00, 2000.00, 30),
(1005, 'Karthik',  'Developer', 1002,      '2021-11-25', 55000.00, NULL,    20);





2. How do you insert 5 sample rows into the DEPT table in SQLite with Indian city names?


Answer:


INSERT INTO DEPT (deptno, dname, loc) VALUES
(10, 'Accounts',    'Mumbai'),
(20, 'IT',          'Bangalore'),
(30, 'Sales',       'Delhi'),
(40, 'HR',          'Chennai'),
(50, 'Support',     'Hyderabad');

Popular posts from this blog