IT 244: Database Management Systems

IT244 – Database Management Systems (Diploma in CS and /or IS)

  1. Course Title:Database Management Systems
  1. Course Code:IT244
  1. Category:IT Compulsory (Both CS and IS Diploma Program)
  1. Length:60 hours
  1. Description:

This course examines database management systems, file processing, data models, query languages, recovery and concurrency, security and integrity, and the development of database using a relational database model.

  1. Rationale:

To prepare students to use real world database systems as well as possess the underlying theory.

  1. Pre-requisite:

7.1141 — Information Systems

7.2142 — Introduction to Programming: Visual Basic .Net

  1. Learning Outcomes:

At the end of this course, students should be very familiar with MS Access (all aspects, including how to make data pages), as well as how to use a SQL server for a client-server relationship.

This part should use Visual Basic to show how to connect to a SQL server. (No need to spend much time on this, because the IT256 course also covers how to connect to a database with Visual Basic. We will set up a SQL server to use for these instances. Also, there is a SQL server on the Linux machine (mySQL) which is good for SQL practicing)

  1. Content:
  1. Database Systems
  2. The Evolution of Database Systems
  3. Early Database Management Systems
  4. Relational Database Systems
  5. Small and Big Systems
  6. Client-Server and Multi-Tier Architecture
  7. Overview of Database Management Systems
  8. Data-Definition Language Commands
  9. Overview of Query Processing
  10. Storage and Buffer Management
  11. Transaction Processing
  12. The Query Processor
  13. Outline of Database-System Studies
  14. Database Design
  15. Database Programming
  16. Database System Implementation
  17. Information Integration Overview
  18. The Entity-Relationship Data Model
  19. Elements of the E/R Model
  20. Entity Sets
  21. Attributes
  22. Relationships
  23. Entity-Relationship Diagrams
  24. Instances of an E/R Diagram
  25. Multiplicity of Binary E/R Diagrams
  26. Multiway Relationships
  27. Roles in Relationships
  28. Attributes on Relationships
  29. Design Principles
  30. Faithfulness
  31. Avoiding Redundancy
  32. Simplicity
  33. Choosing Correct Relationships
  34. The Modeling of Constraints
  35. Classification of Constraints
  36. Keys in the E/R Modeling
  37. Representing Keys in the E/R Modeling
  38. Single-value Constraints
  39. Referential Integrity
  40. Referential Integrity in E/R Diagrams
  41. Weak Entity Sets
  42. Causes of Weak Entity Sets
  1. The Relational Data Model
  2. Basics of the Relational Model
  3. Attributes
  4. Schemas
  5. Tuples
  6. Domains
  7. Equivalent Representations of a Relation
  8. Relation Instances
  9. Integrity Constraints (* From last year notes)
  10. From E/R Diagrams to Relational Designs
  11. From Entity Sets to Relations
  12. From E/R Relationships to Relations
  13. Combining Relations
  14. Function Dependencies
  15. Definition of Functional Dependency
  16. Keys of Relation
  17. Discovering Keys for Relations
  18. Design of a Relational Database Schema
  19. Anomalies
  20. Decomposing Relations (Normalization)
  21. Boyce-Codd Normal Form
  22. Decomposition in BCNF
  23. Third Normal Form
  24. Multivalued Dependencies
  25. Definition of Multivalued Dependencies
  26. Reasoning of Multivalued Dependencies
  27. Fourth Normal Form
  28. Decomposition into Fourth Normal Form
  29. Other Data Models
  30. XML
  31. Semantic Tags
  32. Well-Formed XML
  33. Document Type Definitions
  34. Using a DTD
  35. Attribute Lists
  36. Relational Algebra
  37. Algebra of Relational Algebra
  38. Basics of Relational Algebra
  39. Set Operations of Relations
  40. Projections
  41. Selections
  42. Cartesian Product
  43. Natural Joins
  44. Structured Query Language (SQL)
  45. Simple Queries in SQL
  46. Projection in SQL
  47. Selection in SQL
  48. Comparing of Strings
  49. Dates and Times
  50. Ordering the Output
  51. Queries with Multiple Relations
  52. Products and Joins in SQL
  53. Clearing up Attributes
  54. Tuple Variables
  55. Union, Intersection and Difference Queries
  56. Sub-Queries
  57. Sub-queries Introduction
  58. Full-Relation Operations
  59. Eliminating Duplicates
  60. Grouping and Aggregation in SQL
  61. Aggregation Operations
  62. Grouping
  63. HAVING Clauses
  64. Database Modifications
  65. Insertion
  66. Deletion
  67. Updating
  68. Defining a Relation Schema in SQL
  69. Data types
  70. Simple Table Declarations
  71. Modifying Relation Schemas
  72. Keys and Foreign Keys
  73. Declaring Primary Keys
  74. UNIQUE Keys
  75. Enforcing Key Constraints
  76. Declaring Foreign-Key Constraints
  77. Maintaining Referential Integrity
  78. System Aspects of SQL
  79. SQL in Programming Languages
  80. The Impedance Mismatch Problem
  81. The SQL/Host Language Interface
  82. Transactions in SQL
  83. Atomicity
  84. Transactions
  85. Read-Only Transactions
  86. Concurrency Control (* from notes last year)
  87. Disaster Recovery (* from notes last year)
  88. How to protect data
  89. How to backup data properly
  90. Security and User Authorization in SQL
  91. Privileges
  92. Creating Privileges
  93. Granting Privileges
  94. Revoking Privileges
  1. Requirements :

Students will be required to:

-attend at least 80% of the classes to be eligible to take the final examination.

-complete both the supervise and unsupervise practical hours. [2 hours supervise practical, 8 hours unsupervise practical per week]

-complete all assessment provided.

-pass both the course work and the Final Exam to pass the course.

  1. Assessment:

The course will be assessed according to the following:

  1. Course Work 40%
  1. Test – 15%
  2. Project Assignment Par 1 – 10%
  3. Project Assignment Part 2 – 15%
  4. Final Exam60%

TOTAL MARK100%

  1. Course Evaluation:

Students will be given the opportunity to anonymously evaluate the course and their feedback from these evaluations will be used appropriately to improve the course.

  1. Recommended Reading:

A First Course in Database Systems. Jeffrey Ullman, Jennifer Widom (Third Edition)

  1. Course Costs:

This course does not entail any additional costs

  1. Staffing:

This course will be taught by either a TIHE lecturer or an appropriately experienced and qualified lecturer will be contracted.

  1. Resource Implications:

Resource implications for this course include the following:

Resource Required / Resource Available / Implications
One Textbook for each student / Only one Set for the Tutor / Tutor :-
  • Summarize notes for the students
  • Recommend online resource assistant

1 Computer per Student during Class / Student share computers during class
Lecture Notes / Lecture Notes are available from the course link at the beginning of the Semester.
Past Exam Papers / Past Exam Papers
Internet Access / Internet Access 24-7
  1. Consultation Process:

The following people were consulted in the review of this course:

17.1Principal, D Principal, Course Coordinator, Course Tutor:TIHE

1