DATABASE CONCEPTS UNIT 4
What is data?
- Attendance Record - record attendance for future reference
- Search histories - keep a track of customer preferences
- Order details - to keep a record
- Marks of a student -- performance
- Covid related data -- Analysis and take decisions
- Bank maintain data about customers - record
It is information stored for future references. It describes an entity. It is a combination of characters,
digits, special characters.
Use of data
- Taking decisions
- Machine learning
- Defining strategies
- Record Maintenance
Database vs Non database applications
- Bank
- Reservations system
- School management system
- Inventory management
- Sales monitoring system
- Shares management
- LMS
- Calculator
Limitations of manual databases
- Lot of efforts, human resources, space to keep those files, time to enter data
- Sharing of data was a tedious task
- Difficult to update data
- Difficulty in accessing
- Possibility of human errors
- Data redundancy
- Data isolation
- Data inconsistency
Uses of electronic databases
- Saving space, store large amount of data
- Conserving resources
- Easy editing/input of data/calculation of data
- Data sharing
- Data accessibility
- Reduction in data redundancy, data inconsistency
- Less errors
- Improved Security
- Backup services / recovery services
Database management system
A Database Management System allows a person to organize, store, and retrieve data from a
computer.
It is a way of communicating with a computer's stored memory.
Some examples: MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access, MongoDB.
Types of database models
- Hierarchical database model
- Network model
- Relational model
- Object-oriented database model
- NoSQL model
Relational model of DBMS
The term "relational database" was invented by E. F. Codd at IBM in 1970.
- Data is presented as relations (tables with rows and columns)
- Provides relational operators to manipulate the data
Popular RDBMS:
- DB2, Informix (IBM)
- Oracle, RDB (Oracle)
- SQL Server, Access (Microsoft)
RDBMS
- Organizes data into tables (relations)
- Rows are called records or tuples
- Columns are called attributes
- Each table represents an entity type
- Rows represent instances, columns represent values of attributes
RELATIONAL DATABASE MODEL CONCEPTS
- Attribute: A column in a table
- Table: Contains rows (records) and columns (attributes)
- Tuple: A single row in a table
- Relation Schema: Name of relation and its attributes
RELATIONAL DATABASE MODEL CONCEPTS
- Degree: Number of attributes in the relation
- Cardinality: Number of rows in the table
- Column: Set of values for an attribute
- Relation instance: Set of tuples in a table
- Relation key: One or more attributes identifying each row
- Attribute domain: Pre-defined values and scope of an attribute
Type of keys in a Relation
- Primary Key: Uniquely identifies a row, cannot be NULL or duplicated
Type of keys in a Relation
- Composite Primary Key: More than one attribute together form a primary key
Type of keys in a Relation
- Candidate Key: Fields eligible to act as a primary key
- Can't be duplicated, multiple candidate keys possible
Type of keys in a Relation
- Alternate Key: Candidate key that is not the primary key
- Foreign Key: Field that refers to primary key in another table
REFERENTIAL INTEGRITY
System of rules to ensure relationships between tables are valid.
Enforced by foreign keys.
Practice Question 1
Identify the following:
- Candidate keys
- Primary Key
- Alternate Key
Practice Question 3
Identify the following:
- Candidate keys
- Primary Key
- Degree
- Cardinality
Practice Question 4
Table: STUDENT {SID, FNAME, LNAME, COURSEID}
Find the following:
- Candidate keys
- Primary Key
- Foreign key
- Degree of the table
Union
Combines results of two SELECT queries into a new set with unique values.
Rules:
- Same number of columns
- Same domain for corresponding columns
Intersection
Returns common values between results of two SELECT queries.
Rules:
- Same number of columns
- Same domain for corresponding columns
Minus
Returns values in one table that are not in another.
Rules:
- Same number of columns
- Same domain for corresponding columns
Cartesian Product
Produces all combinations of rows from two tables.
Resultant rows = R1 rows * R2 rows
Resultant columns = R1 cols + R2 cols
Set Operations Notes
MINUS, UNION, and INTERSECT will sort the results.
ORDER BY can only be used at the end of compound queries.
