2: Chapters
- Page ID
- 161273
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\dsum}{\displaystyle\sum\limits} \)
\( \newcommand{\dint}{\displaystyle\int\limits} \)
\( \newcommand{\dlim}{\displaystyle\lim\limits} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\(\newcommand{\longvect}{\overrightarrow}\)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)- 2.1: Before the Advent of Database Systems
- This page outlines the transition from file-based systems to database systems in data management, addressing issues like redundancy, integrity, and security in file systems. It highlights the benefits of databases in managing large data volumes and defines key terms related to data management. The text emphasizes the crucial role of databases in business and other sectors for effective data storage and manipulation.
- 2.2: Fundamental Concepts
- This page explains that a database is a structured collection of related data used for organizational activities, focusing on properties like logical coherence and consistency. It details how data is organized into fields and tables and highlights the role of a Database Management System (DBMS) in creating and maintaining databases, facilitating efficient data retrieval. Important terms such as database, DBMS, and data elements are introduced.
- 2.3: Characteristics and Benefits of a Database
- This page emphasizes effective information management through database management systems (DBMS), highlighting their advantages over traditional file systems, including self-describing data and program-data independence. It outlines key features like data integrity, access control, and redundancy management, essential for concurrent user access and data consistency.
- 2.4: Types of Data Models
- This page outlines two main types of data models: high-level conceptual models, such as the entity-relationship model, which focus on human-perceived data representation, and record-based logical models, which include relational, network, and hierarchical types that reflect computer data storage. It defines key terms and includes exercises to promote deeper understanding of these concepts.
- 2.5: Data Modelling
- This page explains database design, focusing on data modeling as the foundation, which involves identifying entities, relationships, and constraints. It details the transition from high-level to physical models, addressing types of data independence and schema concepts. Additionally, it is based on "Database System Concepts" with contributions from Adrienne Watt, including data abstraction and exercises for comprehension.
- 2.6: Classification of Database Management Systems
- This page classifies database management systems (DBMS) by data models, user numbers, and distribution methods, emphasizing the relational model's dominance in systems like Oracle and MySQL, with less use of object-oriented models. It differentiates between single-user and multiuser systems and discusses centralized versus distributed databases, including homogeneous and heterogeneous types. The page also includes key terms and exercises for reinforcement.
- 2.7: The Relational Data Model
- This page explains the relational data model created by C. F. Codd in 1970, which underpins modern databases and SQL. It structures data in inter-related tables with rows and columns, where each table's degree refers to the number of attributes. Key features include the absence of duplicate rows and adherence to defined domains for table entries. Grasping these concepts is essential for effective database management.
- 2.8: The Entity Relationship Data Model
- This page provides a comprehensive overview of the entity relationship (ER) data model crucial for database design, focusing on entities, relationships, and various types of keys (primary, foreign, candidate, composite). It explains the significance of attributes, null values, and their impact on relational databases. Key concepts such as one-to-many and many-to-many relationships are discussed, along with integrity in database management.
- 2.9: Integrity Rules and Constraints
- This page details constraints within the relational model, focusing on their importance for data integrity and semantics. It covers various integrity constraints like domain, entity, and referential integrity, and illustrates their application through database examples. The page also introduces cardinality and connectivity concepts regarding table relationships, including optional and mandatory relationships.
- 2.10: ER Modelling
- This page emphasizes the significance of functional dependency in the entity-relationship model for effective database design. It highlights how FDs define relationships among attributes, aid in schema normalization, and help reduce redundancy while preventing anomalies. Poor design risks data inconsistencies, and the page advocates for proper normalization through the use of separate tables for entities to ensure data integrity. Key terms are defined, and exercises for practice are provided.
- 2.11: Functional Dependencies
- This page discusses functional dependency (FD) as the relationship between attributes, particularly involving primary keys and non-key attributes. It defines FD and outlines key rules such as reflexivity, augmentation, transitivity, union, and decomposition, which aid in managing dependencies. Additionally, Armstrong's axioms are highlighted as tools for deriving these dependencies. Mastery of these concepts is essential for database normalization and reducing data redundancy.
- 2.12: Normalization
- This page emphasizes the significance of normalization in database design to prevent anomalies, detailing the most common normal forms: 1NF, 2NF, 3NF, and BCNF. It explains how each form addresses redundancy and dependencies in data, including the elimination of repeating groups and transitive dependencies.
- 2.13: Database Development Process
- This page outlines the software development life cycle (SDLC) and database development processes, emphasizing structured approaches like the waterfall model. It highlights critical phases such as requirements gathering, design, and implementation, with a focus on design criteria for database development. The challenges of achieving desirable database properties are discussed, along with methods for data population and guidelines for creating ER diagrams.
- 2.14: Database Users
- This page discusses various types of database users, including end users who query and report data, application users who utilize existing programs, sophisticated users who use query languages, application programmers who create data management applications, and database administrators (DBAs) responsible for overseeing access and resources. It also defines key terms related to these roles within the database ecosystem.
- 2.15: SQL Structured Query Language
- This page covers Structured Query Language (SQL) as a crucial tool for managing relational database data, emphasizing Data Definition Language (DDL) for database and table creation. Key DDL commands such as CREATE DATABASE and CREATE TABLE are detailed, with examples including constraints like UNIQUE and NOT NULL to ensure data integrity. Various other constraints like FOREIGN KEY, CHECK, and DEFAULT also enhance data management.
- 2.16: SQL Data Manipulation Language
- This page provides a comprehensive overview of SQL Data Manipulation Language (DML), focusing on key commands like SELECT, INSERT, UPDATE, and DELETE, their syntax, and practical examples. It covers filtering and sorting data using clauses like WHERE, GROUP BY, and HAVING. The page also details SQL functions for date and mathematical operations, table joins, and various types of queries (UNION, INTERSECT).
- 2.17: Appendix A - University Registration Data Model Example
- This page details the data requirements for an e-learning university, covering student and staff information, course management, and performance tracking across four regions. It highlights the collection of registration data, course details, and enrollment constraints.
- 2.18: Appendix B - Sample ERD Exercises
- This page offers exercises on creating ERDs for a manufacturing company and a car dealership. The first exercise involves tracking product, component, and supplier information with defined relationships and cardinalities. The second exercise models the dealership's sales and service processes, focusing on customer-salesperson interactions and invoicing. Both exercises emphasize visually representing entities, keys, and their relationships.
- 2.19: Appendix C - SQL Lab with Solution
- This page discusses the creation and management of an "Orders" database, focusing on defining tables with primary and foreign key constraints and data integrity rules. It includes SQL commands for creating and managing tables for customers, suppliers, shippers, products, and orders, as well as querying capabilities like retrieving orders by year and customer purchases. Additionally, it addresses employee management and integrating sales totals into the orders table.