Download Database Systems: A Practical Approach - Course Overview and Objectives and more Study Guides, Projects, Research Data Mining in PDF only on Docsity!
Course Title: Database systems
Instructor: Ms. Aqsa Iftikhar
Lectures on TBD Office Hours: Friday (1:00 p.m. to 2:30 p.m.), and by appointment.
Aim of the course
This course covers the fundamentals of database architecture, database management systems, and database systems. Principles and methodologies of database design, and techniques for database application development. This course is designed to provide students database application and design skills through the use of MySQL and SQL Server database software. Learning outcomes associated with the conceptual base of the course are: List and explain the fundamental concepts of a relational database system. Utilize a wide range of features available in a DBMS package. Analyze database requirements and determine the entities involved in the system and their relationship to one another. Develop the logical design of the database using data modelling concepts such as entity-relationship diagrams. Create a relational database using a relational database package. Manipulate a database using MySQL & SQL. Assess the quality and ease of use of data modelling and diagramming tools Learning outcomes associated with the Practical work of the course are: Database design and implementation using SQL. The practical application of SQL queries. They will be able to develop connectivity of database and storing data in it. Using python and Django make a complete real-time web application.
Plagiarism policy
I have no tolerance for plagiarism. Plagiarized assignments will be given a failing grade. Two instances of plagiarism will result in a disciplinary hearing. http://hec.gov.pk/english/services/faculty/Documents/Plagiarism/Plagiarism %20Policy.pdf
Course Outlines and material on plagiarism policies will be handed to students before the course begins. All material will remain on the course website as well.
Note:
I reserve the right to change the course readings or outline based on the performance of the class. Deadlines for assignments and projects will be strictly followed. Failure to submit the assignment/project will lead to the student being given a failing grade for said assignment.
Grading Policy:
Mid-term Exam 20% Final Exam 35% Project 20% Assignments 10% Quizzes 5% Class Participation 5% Presentations 5%
Attendance Policy:
80% attendance is required. Failure to meet this requirement will result in a failing grade for you.
Course Website:
The topics that are to be covered during the course will be uploaded to Google Classroom.
Weekly structure:
Week 1 Introduction to Database Differences between data and information. File-Based file system Limitations of the file-based system Database Management systems. DDL & DML(Introduction)
Week 2 (^) The Three-Level ANSI-SPARC Architecture External Level, Conceptual, Internal Level. Schemas, Mapping, and instances Data Independence Database Languages Functions of a DBMS Components of a DBMS Multi-User DBMS Architecture Teleprocessing File-Server Architectures Traditional Two-Tier Client-Server Architecture Three-Tier Client-Server Architecture Transaction Processing Monitors Reading Section^ 3.1-^ 3.1.7,^ 3.6^ from^ the textbook Lecture PPT Textbook: Database Systems A Practical Approach to Design, Implementation, and Management, 4th Edition, Thomas Connolly, Carolyn Begg, Addison Wesley, 2005. Reference Material: Fundamentals of Database Systems (7th^ Edition) by Ramez Elmasri, Shamkant B. Navathe Objectives Students will learn Three-Level architecture, this framework is used to describe the structure of a specific database system. The three- schema architecture is also used to separate the user applications and physical database. Understand the concept of schema and mapping, how actually data maps on the database. Discuss different database languages and functions of DBMS Discuss different DBMS Architecture and their comparison.
Week 3 Relationship Types Degree of Relationship Types Recursive Relationship Attributes Simple and composite Attributes Single-Valued and Multi-Valued Attributes Derived Attributes Strong and Weak Entity Types Attributes on Relationship Structural Constraints One-to-One (1:1) Relationships One-to-Many (1:) Relationships Many-to-Many (:*) Relationships Multiplicity for Complex Relationships Cardinality and Participation Constraints Readings Section 12.4-12.6 from the Textbook Examples are given to solve Lecture PPT Textbook: Database Systems A Practical Approach to Design, Implementation, and Management, 4th Edition, Thomas Connolly, Carolyn Begg, Addison Wesley, 2005. Reference Material: Fundamentals of Database Systems (7th^ Edition) by Ramez Elmasri, Shamkant B. Navathe Objectives From Lecture 1,2 students who understand what is data know will learn how data is represented in the database. Learn what is entities, attributes, and different types of attributes. Solved
Objectives Students will learn the relational database model, which is used to organizes data into tables that can be linked or related, based on data common to each. They will learn how to develop a database that models some real-world system. students recall entities and attributes that involve breaking the real-world system into tables and fields and determining how the tables relate to each other. Lecture 3 concepts used for making a table. Students will learn the Keys concept in databases to uniquely identify the table. In the relational model, keys are important because they are used to ensure that each row in a table is uniquely identifiable. They are also used to establish relationships among tables and to ensure the integrity of the data. Therefore, a proper understanding of the concept and use of keys in the relational model is very important. The keys role is based on a concept known as determination, when the statement" A determines B" indicates that if you know the value of attribute A, you can loop up (determine) the value of attribute B, for example as in table student Students will learn the Views in database systems. Views are used for security purposes because they provide encapsulation of the name of the table. Data is in the virtual table, not stored permanently. Views display only selected data.
Weeks 5 (^) The Extended Entity-Relationship Model Entity Super-Types and Subtypes Specialization Hierarchy Inheritance Subtype Discriminator The Extended Entity-Relationship Model Readings Section 13.1- 13.3 from the Textbook Examples/ Excerpts Lecture PPT Textbook: Database Systems A Practical Approach to Design, Implementation, and Management, 4th Edition, Thomas Connolly, Carolyn Begg, Addison Wesley, 2005. Reference Material: Fundamentals of Database Systems (7th^ Edition) by Ramez Elmasri, Shamkant B. Navathe Objectives Students will create schemas that are ERD and EERD. The Extended Entity-Relationship (EER) model is a conceptual (or semantic) data model, capable of describing the data requirements for a new information system in a direct and easy-to-understand graphical notation. Data requirements for a database are described in terms of a conceptual schema, using the EER model. All concepts learn in lecture 1,2,3, were used here to create ERD and EERD diagrams. Make a model that will map in the database. The concept of data gathering, entities, attributes, keys, and the relationship between entities were used to create a schema.
structure and design of relational databases. Understand the importance and major issues of database security and the maintenance of data integrity. This lecture will make them understand how data is stored in the database and how we get data from a database by writing SQL queries. The tool used for Database creation is SQL Server and Installation of SQL server in this lecture. Students will present the Idea of the project. Week 7-8 (^) The Relational Algebra Unary Operations Set Operations Join Operations Division Operation Aggregation and Grouping Operations Summary of the Relational Algebra Operations Readings Section 5.1-5.1.6 from the textbook Lecture PPT Examples/ Excerpt Textbook: Database Systems A Practical Approach to Design, Implementation, and Management, 4th Edition, Thomas Connolly, Carolyn Begg, Addison Wesley, 2005. Reference Material: Fundamentals of Database Systems (7th^ Edition) by Ramez Elmasri, Shamkant B. Navathe Objectives Students will learn Relational Algebra that is a procedural query language, which takes instances of relations as input and yields instances of relations
as output. It uses operators to perform queries. They accept relations as their input and yield relations as their output. Relational algebra is very important for several reasons:
- it provides a formal foundation for relational model operations.
- It is used as a basis for implementing and optimizing queries in the query processing and optimization modules that are integral parts of relational database management systems (RDBMSs)
- some of its concepts are incorporated into the SQL standard query language for RDBMSs. Students will be able to write relational algebra queries. Week 9 (^) Cartesian /Cross Product Joins Types of Joins (Natural, theta, outer joins) Query trees Translating relational algebra to query trees Readings Lecture PPT Examples/ Excerpts Textbook: Database Systems A Practical Approach to Design, Implementation, and Management, 4th Edition, Thomas Connolly, Carolyn Begg, Addison Wesley, 2005. Reference Material: Fundamentals of Database Systems (7th^ Edition) by Ramez Elmasri, Shamkant B. Navathe Objectives Lecture 8 of Relational Algebra
structuring a relational database following a series of so-called normal forms to reduce data redundancy and improve data integrity. By learning normalization makes sure that all of your data looks and reads the same way across all records. Learn different types of Normal forms. Normalize the data up to 2NF. Week 11 (^) Conversion to Third Normal Form Higher-Level Normal Forms Fourth Normal Form(4NF) Normalization and Database Design Normalization Practice Readings Section 15.3-15.4.2 from the Textbook Lecture PPT Examples/ Excerpts Textbook: Database Systems A Practical Approach to Design, Implementation, and Management, 4th Edition, Thomas Connolly, Carolyn Begg, Addison Wesley, 2005. Reference Material: Fundamentals of Database Systems (7th^ Edition) by Ramez Elmasri, Shamkant B. Navathe Objectives Students will learn 3NF and 4NF, 5th NF. Normalize the data up to 4NF. Students will submit an Assignment given to them related to normalization.
Week 12 (^) What is Transaction? Transaction States Properties of Transactions Concurrency control and why it is needed? The lost update The dirty read problem The inconsistent analysis problem Why is recovery needed? Types of Failure Readings Section 22.1 from the Textbook Lecture PPT Textbook: Database Systems A Practical Approach to Design, Implementation, and Management, 4th Edition, Thomas Connolly, Carolyn Begg, Addison Wesley, 2005. Reference Material: Fundamentals of Database Systems (7th^ Edition) by Ramez Elmasri, Shamkant B. Navathe Objectives Students will learn the primary objective of TPS that is to capture, gather, process, and store transactions. Students will understand the different states of transactions, properties, and why successful execution of the transaction is needed. Problems arise if transactions are not completed and how to cover that problem. Week 13 (^) Concurrency Control Techniques 2PL 2PL to remove
Security in Database Database Planning, Design, and Administration The Information Systems Lifecycle The Database System Development Lifecycle Database Planning System Definition User View Requirements Collection and Analysis Centralized Approach View Integration Approach Database Design Approaches to Database Readings Section 22.2. Lecture PPT Textbook: Database Systems A Practical Approach to Design, Implementation, and Management, 4th Edition, Thomas Connolly, Carolyn Begg, Addison Wesley, 2005. Reference Material: Fundamentals of Database Systems (7th^ Edition) by Ramez Elmasri, Shamkant B. Navathe Objectives Students will understand Timestamp- based Concurrency Control algorithms. Database systems life cycle and the purpose of an SDLC methodology is to provide IT Project Managers with the tools to help ensure successful implementation of systems that satisfy University strategic and business objectives. Week 15 (^) Data Modeling Phases of Database Design DBMS Selection Selecting the DBMS
Application Design Transaction Design User Interface Design Guidelines Prototyping Implementation Project Presentation Readings Section 10.1-10.9 from the Textbook Lecture PPT Objectives Students will analyze the Purpose of DBMS selection because each company has different objectives, different objectives need a unique treatment/service from the database. The Scope of the task that will be performed is important to be studied as well. Both the Objectives and the Scope of the Study will be created based on the criteria on the user requirements. When an enterprise decided to use the Database model in their Business, they will need the DBMS system, but DBMS have so many varieties, and it is not a simple task for choosing the DBMS, because there is some risk that may occur when the DBMS is selected is not compatible. After all, it doesn’t match the company’s strategies. In many ways, the database has become a commodity. Products differ in price, performance, ease of database administration, and functionality.
