Database Management Systems

Course ID
CSGE 201
Level
Undergraduate
Program
B.Sc. CS (Hons.)
Semester
Second
Credits
6.0
Paper Type
Generic Elective
Method
Lecture & Practical

Unique Paper Code: CSGE 201

The course introduces the concepts of database management systems to students, focusing on basics such as the importance and significance of a database, data model, schema creation and normalization.

Learning Outcomes:

At the end of the course, students should be able to:

  • Describe the features of database management systems.
  • Differentiate between database systems and file systems.
  • Model an application’s data requirements using conceptual modelling tools like ER diagrams and design database schemas based on the conceptual model.
  • Write queries in relational algebra / SQL.
  • Normalize a given database schema.

Course Contents

Unit 1
Unit 2
Unit 3
Unit 4
Unit 5
Unit 6

Unit 1

Introduction: Introduction to database, relational data model, DBMS architecture, data independence and data abstraction, DBA, database users, end users, front end tools.

Unit 2

Data Modelling: Entity types, entity set, attribute and key, relationships, relation types, ER diagrams, database design using ER diagrams.

Unit 3

Relational Data Model: Relational model concepts, relational constraints, primary and foreign key, candidate key, alternate, composite, super-key.

Unit 4

Data redundancy, Normalization: 1NF, 2NF, 3NF.

Unit 5

Structured Query Language: Introduction to SQL, concepts of Data Definition Language (DDL) and Data Manipulation Language (DML), DDL queries like create a data base, drop a database, create table, drop table, alter table, DML queries like inserting data in a table, update in a table, delete data from a table, filter data.

Unit 6

Structured Query Language (continued..): Create relationships between database tables, auto increment, check, Null values, aggregate functions – min, max, count, average, sum, nested sub-queries, group by, having, exists, case, order by. Join operations – inner, left join, right join, natural join and Cartesian product. Overview of forms and reports.

Practicals

Lab List 1

  1. Create a database having two tables with the specified fields, to computerize a library system of a Delhi University College.
    LibraryBooks (Accession number, Title, Author, Department, PurchaseDate, Price)
    IssuedBooks (Accession number, Borrower)
    a) Identify primary and foreign keys. Create the tables and insert at least 5 records in each table.
    b) Delete the record of book titled “Database System Concepts”.
    c) Change the Department of the book titled “Discrete Maths” to “CS”.
    d) List all books that belong to “CS” department.
    e) List all books that belong to “CS” department and are written by author “Navathe”.
    f) List all computer (Department = “CS”) that have been issued.
    g) List all books which have a price less than 500 or purchased between “01/01/1999” and “01/01/2004”.
  2. Create a database having three tables to store the details of students of Computer Department in your college, as per the given schema.
    Personal information about Student (College roll number, Name of student, Date of birth, Address, Marks(rounded off to whole number) in percentage at 10 + 2, Phone number) Paper Details (Paper code, Name of the Paper)
    Student’s Academic and Attendance details (College roll number, Paper code, Attendance, Marks in home examination).
    a) Identify primary and foreign keys. Create the tables and insert at least 5 records in each table.
    b) Design a query that will return the records (from the second table) along with the name of student from the first table, related to students who have more than 75% attendance and more than 60% marks in paper 2.
    c) List all students who live in “Delhi” and have marks greater than 60 in paper 1.
    d) Find the total attendance and total marks obtained by each student.
    e) List the name of student who has got the highest marks in paper 2.
  3. Create the following tables and answer the queries given below:
    Customer (CustID, email, Name, Phone, ReferrerID)
    Bicycle (BicycleID, DatePurchased, Color, CustID, ModelNo) BicycleModel (ModelNo, Manufacturer, Style)
    Service (StartDate, BicycleID, EndDate)
    a) Identify primary and foreign keys. Create the tables and insert at least 5 records in each table.
    b) List all the customers who have the bicycles manufactured by manufacturer “Honda”.
    c) List the bicycles purchased by the customers who have been referred by customer “C1”.
    d) List the manufacturer of red colored bicycles.
    e) List the models of the bicycles given for service
  4. Create the following tables, enter at least 5 records in each table and answer the queries given below.
    EMPLOYEE ( Person_Name, Street, City )
    WORKS ( Person_Name, Company_Name, Salary ) COMPANY ( Company_Name, City )
    MANAGES ( Person_Name, Manager_Name )
    a) Identify primary and foreign keys.
    b) Alter table employee, add a column “email” of type varchar(20).
    c) Find the name of all managers who work for both Samba Bank and NCB Bank.
    d) Find the names, street address and cities of residence and salary of all employees who work for “Samba Bank” and earn more than $10,000.
    e) Find the names of all employees who live in the same city as the company for which they work.
    f) Find the highest salary, lowest salary and average salary paid by each company.
    g) Find the sum of salary and number of employees in each company.h) Find the name of the company that pays highest salary.
  5. Create the following tables, enter at least 5 records in each table and answer the queries given below.
    Suppliers (SNo, Sname, Status, SCity) Parts (PNo, Pname, Colour, Weight, City) Project (JNo, Jname, Jcity)
    Shipment (Sno, Pno, Jno, Qunatity)
    a) Identify primary and foreign keys.
    b) Get supplier numbers for suppliers in Paris with status>20.
    c) Get suppliers details for suppliers who supply part P2. Display the supplier list in increasing order of supplier numbers.
    d) Get suppliers names for suppliers who do not supply part P2.
    e) For each shipment get full shipment details, including total shipment weights.
    f) Get all the shipments where the quantity is in the range 300 to 750 inclusive.
    g) Get part nos. for parts that either weigh more than 16 pounds or are supplied by suppliers S2, or both.
    h) Get the names of cities that store more than five red parts.
    i) Get full details of parts supplied by a supplier in London.
    j) Get part numbers for part supplied by a supplier in London to a project in London.
    k) Get the total number of project supplied by a supplier (say, S1).
    l) Get the total quantity of a part (say, P1) supplied by a supplier (say, S1).

Additional Information

Text Books


Bayross, I. (2010) SQL, Pl/SQL the Programming Language of Oracle. 4th edition. BPB Publications.
Elmsasri, R., & Navathe, S.(2017). Fundamentals of Database Systems. 7th edition. Pearson Education.
Silberschatz, A., Korth, H. F., & Sudarshan, S. (2011), Database System Concepts. 6th edition. Tata McGraw-Hill Education.

Additional Resources


Date, C.J., Kanman, A. & Swamynathan, S. (2006). An Introduction to Database Systems.8th edition. Pearson Education.
Ramakrishnan, R. Gehrke, J. (2014), Database Management Systems. 3rd edition. Tata McGraw Hill Education.
MWidenius, M., Axmark, D., Cole, J., Lentz, A., & Dubois, P. (2002). MySQL Reference Manual. O’Reilly Community Press.

Teaching Learning Process


Use of ICT tools in conjunction with traditional class room teaching methods
Interactive sessions
Class discussions

Assessment Methods

Written tests, assignments, quizzes, presentations as announced by the instructor in the class

Keywords

Databases, E-R diagrams, normalization, structured query language.

Disclaimer: Details on this page are subject to change as per University of Delhi guidelines. For latest update in this regard please refer to the University of Delhi website here.

judi slot online Capsa Susun Bandar Ceme Online Terpercaya agen ion casino ionclub Slot Online idn poker sbobet casino slot online https://run3-game.net/