IT 3002 Database Systems

• IT 3002 is the course code for Database Systems.
• The course was presented on November 24, 2024, by Amal Nishantha Pattividana.
• The current version of the presentation is 2024 - 1.0.2.

Course Details

• IT 3002 is the course code.
• The course name is Database Systems.
• The course has 3 credits.
• The course is at level 3.
• There are 30 hours of theory and 30 hours of practical lectures.
• Amal Nishantha Pattividana is the lecturer
• Contact: [email protected].

Course Contents

• Databases and DBMSs will be covered.
• This includes introduction, database requirements, and data independency.
• DBMS characteristics and advantages.
• DBMS classifications, including relational and other systems.
• Relational Model Concepts: types, relations, relational constraints, and relational operators.
• Primary Key, Foreign Key, and Indexing.
• Relational Database Design includes phases, entity relationship model, ER diagrams, functional dependency, and normalization.
• Structured Query Language like DDL, DML, and an introduction to DCL and TCL.

Course Contents (continued)

• MySQL/MariaDB will be covered, including introduction and database concepts.
• Database Management Tools like Phpmyadmin and Mysql Administrator.
• SQL: Introduction to SQL and writing SQL.
• Topics include database, tables, queries, views, triggers, procedures, aliases, join operators, group, sort, stored procedures, and data manipulations.
• Interfacing database introduction will be covered.

Course Introduction

• The course provides an understanding of Databases concepts and Database Systems for software development.

Learning Outcomes

• Analyze data and data organization needs.
• Apply the Entity-Relationship Model for building normalised relational data models.
• Use SQL for creating, manipulating, and controlling data.

Additional Readings

• https://www.w3schools.com/mysql/default.asp
• https://www.w3schools.com/sql/default.asp
• "Database Systems" by C. J. Date, Addison Wesley, 8th edition.
• "Fundamentals of Database Systems" by Ramez Elmasri, Shamkant B. Nevathe, 6th Edition, 2011.

Assessment Strategy

• Continuous assessment: 40%
• Final Examination: 60%

Data vs. Information

• Data is unorganized and unrefined raw facts or statistics without context.
• Information is processed and organized content that is meaningful and mostly processed from data.

Database

• A database is a collection of persistent data.
• A database is an organized collection of structured information or data, typically stored somewhere.

Computerized Database

• A computerized database stored and manipulated within a computer system.

Requirement of Databases

• The term "Database" started in 1960 with IBM DBOMP(Database Organization and Maintenance Processor).
• It was an early stage of a hierarchical database system from IBM for DOS.
• Databases are required to:
  • Store collected data.
  • Store processed data (Information).
  • Provide easy access and updates.
  • Re-use data.
  • Ensure security and protection of data.
  • Manage data.
  • Transport and share data.

DataBase Management System (DBMS)

• A DBMS manages databases through software programs.
• A DBMS creates, alters, and maintains databases for users.
• Database systems and DBMS are integrated for service delivery.

Examples of Database Systems

• Examples include: Oracle, MySQL, MariaDB, Microsoft SQL Server, PostgreSQL, Microsoft Access, MongoDB, SQLite, IBM DB2, INGRES, Informix, Sybase, Apache Cassandra, Firebird SQL, Nero4j, CouchDB, Redis, Cockroach Labs, Google Cloud, NoSQL, AWS RDS, OriendDB, InterBase.

Database System Environment

• Involves users/programmers interacting with the database system through applications and queries.
• DBMS software processes queries and accesses stored data.
• In includes database management services, stored database definitions (meta-data), and the stored data itself.

Characteristics of Database System

• Self-Describing Nature - Catalog and Metadata
• Data Abstraction - Data Independency, Operation Independency
• Multiple Views and Data Sharing
• Transaction Processing - Concurrency Control

Different Players in Database System

• DB System Developers are responsible for Database Management System Design and Implementations, tools and feature development, and providing Database Hardware, Software, and Service.
• DB System Users include Database System Administrators, Database Designers, and Application Program developers.

Advantages of a Database System

• Data shared with different cases
• Minimal Redundancy
• Minimal inconsistency
• Data integrity
• Data security
• Support Transaction
• Data independency
• Data indexing techniques are integrated.
• In modern systems, data maintain services, resource management, user management, and load balancing are included.

Types of DBMS

• Relational database (RDBMS)
• Object oriented database
• Hierarchical databases
• Network databases

Relational DBMS

• Data is logically organized in 2-D tables (relation) with rows (tuples) and columns (attributes).
• Each row holds a single value per attribute.

Relational DBMS Cont.

• Relation: A table with related rows and columns.
• Attributes: Named columns in the relation.
• Domain: Set of acceptable values for the attributes.
• Tuple: A row of a relation.

Object Oriented Database

• Data and information represent in objects used in object-oriented programming.
• Combines relational database with OOP concepts.
• Data controlling uses relational model principles with data encapsulation, inheritance, and polymorphism as OOP concepts.

Hierarchical Database

• Data elements have a one-to-many relationship (1:N).
• Data is organized like a tree.

Network Database

• Data elements have one-to-one (1:1) or many-to-many (N:N) relationships.