Database Management Systems

Course Description

CSC3033 Database Management Systems [3]. A study of database models, designs, organization, normalization, integrity, and distributed database systems. Includes lab project work in design and implementation of relational databases, and software applications that use databases.

Prerequisites: MAT/CSC1053 Elementary Discrete Mathematics, and CSC2013 Foundations of Computer Science 1.

Topic: Information Models

• Information storage and retrieval (IS&R)
• Information management applications
• Information capture and representation
• Metadata/schema association with data
• Analysis and indexing
• Search, retrieval, linking, navigation
• Declarative and navigational queries
• Information privacy, integrity, security, and preservation
• Scalability, efficiency, and effectiveness
• Concepts of Information Assurance (data persistence, integrity)

Learning Objectives:

1. Compare and contrast information with data and knowledge.
2. Critique/defend a small- to medium-size information application with regard to its satisfying real user information needs.
3. Show uses of explicitly stored metadata/schema associated with data
4. Explain uses of declarative queries
5. Give a declarative version for a navigational query
6. Describe several technical solutions to the problems related to information privacy, integrity, security, and preservation.
7. Explain measures of efficiency (throughput, response time) and effectiveness (recall, precision).
8. Describe approaches to ensure that information systems can scale from the individual to the global.
9. Identify issues of data persistence to an organization.
10. Describe vulnerabilities to data integrity in specific scenarios.

Topic: Database Systems

• History and motivation for database systems
• Components of database systems
• DBMS functions
• Database architecture and data independence
• Use of a declarative query language

Learning Objectives:

1. Explain the characteristics that distinguish the database approach from the traditional approach of programming with data files.
2. Cite the basic goals, functions, models, components, applications, and social impact of database systems.
3. Describe the components of a database system and give examples of their use.
4. Identify major DBMS functions and describe their role in a database system.
5. Explain the concept of data independence and its importance in a database system.
6. Use a declarative query language to elicit information from a database.

Topic: Data Modeling

• Data modeling
• Conceptual models (such as entity-relationship or UML)
• Object-oriented model
• Relational data model

Learning Objectives:

1. Categorize data models based on the types of concepts that they provide to describe the database structure—that is, conceptual data model, physical data model, and representational data model.
2. Describe the modeling concepts and notation of the entity-relationship model and UML, including their use in data modeling.
3. Describe the main concepts of the OO model such as object identity, type constructors, encapsulation, inheritance, polymorphism, and versioning.
4. Define the fundamental terminology used in the relational data model .
5. Describe the basic principles of the relational data model.
6. Illustrate the modeling concepts and notation of the relational data model.

Topic: Relational Databases

• Mapping conceptual schema to a relational schema

• Entity and referential integrity

• Relational algebra and relational calculus

• Relational Database design

• Functional dependency

• Decomposition of a schema; lossless-join and dependency-preservation properties of a decomposition

• Candidate keys, superkeys, and closure of a set of attributes

• Normal forms (BCNF)

• Multi-valued dependency (4NF)

• Join dependency (PJNF, 5NF)

• Representation theory

Learning Outcomes:

1. Prepare a relational schema from a conceptual model developed using the entity- relationship model

2. Explain and demonstrate the concepts of entity integrity constraint and referential integrity constraint (including definition of the concept of a foreign key) [Usage]

3. Demonstrate use of the relational algebra operations from mathematical set theory (union, intersection, difference, and Cartesian product) and the relational algebra operations developed specifically for relational databases (select (restrict), project, join, and division) [Usage]

4. Demonstrate queries in the relational algebra [Usage]

5. Demonstrate queries in the tuple relational calculus [Usage]

6. Determine the functional dependency between two or more attributes that are a subset of a relation [Assessment]

7. Connect constraints expressed as primary key and foreign key, with functional dependencies [Usage]

8. Compute the closure of a set of attributes under given functional dependencies [Usage]

9. Determine whether or not a set of attributes form a superkey and/or candidate key for a relation with given functional dependencies [Assessment]

10. Evaluate a proposed decomposition, to say whether or not it has lossless-join and dependency-preservation [Assessment]

11. Describe what is meant by BCNF, PJNF, 5NF [Familiarity]

12. Explain the impact of normalization on the efficiency of database operations especially query optimization [Familiarity]

13. Describe what is a multi-valued dependency and what type of constraints it specifies [Familiarity]

Topic: Query Languages

• Overview of database languages

• SQL (data definition, query formulation, update sublanguage, constraints, integrity)

• Selections

• Projections

• Select-project-join

• Aggregates and
group-by

• Subqueries

• QBE and 4th-generation environments

• Different ways to invoke non-procedural queries in conventional languages

• Introduction to other major query languages (e.g., XPATH, SPARQL)

• Stored procedures

Learning Outcomes:

1. Create a relational database schema in SQL that incorporates key, entity integrity, and referential integrity constraints [Usage]

2. Demonstrate data definition in SQL and retrieving information from a database using the SQL SELECT statement [Usage]

3. Evaluate a set of query processing strategies and select the optimal strategy [Assessment]

4. Create a non-procedural query by filling in templates of relations to construct an example of the desired query result [Usage]

5. Embed object-oriented queries into a stand-alone language such as C++ or Java (e.g., SELECT

Col.Method() FROM Object) [Usage]

6. Write a stored procedure that deals with parameters and has some control flow, to provide a given functionality [Usage]

Topic revision: r5 - 2013-10-24 - RobertKasper
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback