Introduction to Database Concepts

Database Concepts

• A database constitutes a collection of interrelated data items.
• Databases model real-world scenarios through entities like students, courses, and instructors.
• Relationships between entities exist, such as an instructor teaching a course in a specific semester.
• DBMS (Database Management System) is a software package utilized for storing, querying, managing, and analyzing databases.
• A data model represents a real-world scenario within a database.

File Systems vs. DBMS

• Reasons for having a DBMS include avoiding the restrictions of using purely file based systems.
• Data independence means changes to data organization don't require program modifications.
• Data integrity ensures consistency across data files.
• Managing concurrent access prevents inconsistencies.
• Support for transactions as atomic semantic units is provided.
• Crash recovery guarantees consistency and durability following system failures.
• Security and access control ensure appropriate data access for users.

Relational Data Model

• A relational data model is the most widely used model, developed by Ted Codd between 1969-1970.
• Relational data model structures data in relations.
• A relation consists of a table with rows and columns.
• A schema describes the data stored in a column.
• Constraints guarantee the integrity of related data.
• Query languages, both declarative and procedural, are used to query the data.
• Data Manipulation Languages are for modifying database contents.

Relational Database

• A Relational Database is a set of interrelated tables.
• Each relation comprises of a Schema and Instance.

Schema

• It specifies the relation name.
• Includes names and types of all attributes/columns which describe the relation.
• Degree or arity refers to the number of columns or fields.
• Type sets acceptable values for an attribute.
• A null value belongs to every domain.
• Instructor(iid: string, iname: string, irank: string, isalary: integer) is an example of an instructor schema.
• Degree or arity for the example instructor is 4.
• A table with 4 attributes is an N-ary table.

Instance

• Instance is the filling of the schema with data.
• Refers to a set of tuples or rows.
• "Set" implies the order of tuples is not vital and no two tuples are the same.
• Cardinality is the number of tuples or rows.
• Example Instance:
  • Cardinality is 3
  • Degree is 5

Example Relational Database Schemas - Related Tables

• Students(sid: string, name: string, login: string, age: integer, gpa: real)
• Courses(cid: string, cname: string, credits: integer)
• Instructor(iid: string, iname: string, irank: string, isalary: real)
• Enrolled(sid: string, cid: string, grade: string)
• Teaches(iid: string, cid: string, year: integer, semester: string)

Database Keys

• Each tuple in a relation is distinct.
• A key denotes a subset of attributes which uniquely identifies a tuple in the relation.
• For schema R of instance table r, R is a key to r if the attributes in R uniquely identify tuples.
• Candidate key stands for the minimal attribute subset, that uniquely identifies a tuple.
• A table may have more than one candidate key.
• Primary Key refers to the designated "key" of the table.
• Unique keys represent candidate keys that are not selected as the primary key.
• Super Key refers to a primary or unique key augmented by additional attributes.
• A table may have more than one super key.

Keys for Schemas

• Keys are shown in bold italics.
• Schemas and example keys:
  • Students(sid: string, name: string, login: string, age: integer, gpa: real)
  • Courses(cid: string, cname: string, credits: integer)
  • Instructor(iid: string, iname: string, irank: string, isalary: real)
  • Enrolled(sid: string, cid: string, grade: string)
  • Teaches(iid: string, cid: string, year: integer, semester: string)
  • Example student super keys: (sid, name) and (sid, name, gpa)

Keys for Table Teaches (Relationship)

• Teaches(iid: string, cid: string, year: integer, semester: string)
• Choice of key has semantic implications:
  • Key: iid means the instructor ID being key, will not repeat in the table. This corresponds to the sematic implication that each instructor can teach only one course.
  • Key: iid, year implies each instructor can teach one course per year.
  • Key: iid, cid implies each instructor can teach a given course only once.
  • Key: cid, year implies each course can be taught only once every year.
  • Key: year, semester implies in a given semester/year, only one course is taught.
  • Key: iid, cid, year, semester implies in a given semester/year, an instructor cannot teach two instances of the same course.

System Analysts vs. Domain Experts

• System analysts or designers determine logical keys to match semantics of the domain expert.
• If no logical keys are identified, the DBMS can automatically generate unique keys.
• MySQL Example:
  • To auto-generate unique integer primary keys, use AUTO_INCREMENT in MySQL

Foreign Keys

• In two tables R and S.
• Attribute S1 is a key for S.
• Attribute R1 in R refers to values of S1 in S.
• The domain of values of R1 is restricted to those in S1.
• R1 refers to only one tuple in S.
• R1 is termed Foreign key to S.

Example Foreign Keys

• Students(sid: string, name: string, login: string, age: integer, gpa: real)
• Courses(cid: string, cname: string, credits: integer)
• Instructor(iid: string, iname: string, irank: string, isalary: integer)
• Teaches(iid: string, cid: string, year: integer, semester: string)
• Enrolled(sid: string, cid: string, grade: string)
• Cid in Teaches is a Foreign key to cid in Courses, it doesn't have to be the same name or iid
• Sid in Enrolled is a Foreign key to sid in Students, it can be same for cid

Query Languages for the Relational Model

• Two categories of query languages for the relational model are the following

1. Imperative (Procedural):
   • Specifies steps for query evaluation.
   • Relational Algebra serves as mathematical foundation for query engines.
2. Declarative (Non-procedural)
   • Specifies what query needs answered, leaving compilation and optimization to DBMS.
   • A query optimizer is used to re-order operations, guaranteeing a correct answer.
   • Relational Calculus comes in two flavours:
     • Tuple Relational Calculus serves as foundation for SQL.
     • Domain Relational Calculus serves as foundation for QBE.