Database Management Module Flashcards
(41 cards)
What is difference between data and information
Data:
- Raw, unprocessed facts
- Numbers, text, images
Information:
- Processed data that is meaningful
- Game scores, player statistics
What is difference between flat files and relational databases
Flat Files:
- Single table of data
- Simple, but inefficient with large data sets
- Eg. Spreadsheet listing all game players and their scores
Relational Databases:
- Multiple tables linked by relationships
- More efficient & scalable than flat files
- Eg. Tables for players, games, scores, linked by player ID
What is a Relational Database Management System (RDBMS)
Software that uses relational databases to store and manage data
Functions:
- Data storage, retrieval, updating
- Maintaining data integrity & security
What is the role of RDBMS in handling access to data
Access Control:
- Who can access & modify data
- Eg. Only game admins can change high scores
Data Retrieval:
- Efficient querying of data
- Eg. Retrieving top 10 scores from leaderboard
Transaction Management:
- Ensuring data consistency
- Eg. Saving game progress without losing data
What is the independence of data from RDBMS
Data can be modified / moved without changing applications using it
Benefits:
- Flexibility in data management
- Easier upgrades & maintenance
What are entities
Objects / Things in real world with independent existence
Each entity has unique attributes
Eg. Students, Courses, Teachers, in a school database
What are attributes
Properties / Characteristics of entity
Define what data needs to be stored for each entity
Eg. Student entity attributes: StudentID, Name, DoB
What are relationships
Associations between entities
Eg. Relationship between Students, and Courses they enrol in
What are one-to-one relationships
Single record in one table, linked to single record in another table
Has constraints to ensure field in one table corresponds to only one field in another
Eg. Person and their passport
What are one-to-many relationships
Single record in one table, linked to multiple records in another table
Use foreign keys to link related records in different tables
Eg. Single teacher can teach multiple courses
What are many-to-many relationships
Multiple records in one table, linked to multiple records in another table
Use junction table to breakdown many-to-many into two one-to-many relationships
Eg. Students enrolling into multiple courses, each course has multiple students
What are tables
Collection of related data held in structured format within database
Implementation of entities
Has fields (Columns), and records (Rows)
Tables store data for entities
Ensured by primary & foreign keys, constraints
Field (Columns): Define type of data to be stored
- Eg. Student Table with fields; StudentID, Name, DoB, Email
Records (Rows): Each row is data entry for table
What is the hierachical structure of data
1) Field / Attribute:
- Smallest data unit
- Eg. StudentID in student table
2) Record:
- Single, complete set of fields
- Eg. Single student’s details
3) Table / Entity:
- Collection of related records
- Eg. Student table with all student records
4) Database:
- Collection of related tables
- Eg. School database with Students, Courses, Teachers tables
What are the different datatypes in databases
Integer:
- Whole numbers
- Eg. StudentID
Float:
- Decimal numbers
- Eg. GPA
Boolean:
- True / False values
- Eg. Enrollment Status
Text:
- Alphanumeric characters
- Eg. Student Address
Date:
- Date values
- Eg. Date of Birth
Choosing Data Types:
- Ensures data integrity & efficiency
What are primary keys
Uniquely identifies each record in a table
Contains unique values, cannot contain null values
Each record is unique, easily identifiable
Each table has 1 primary key
What are foreign keys
Links records between two tables
Establishes relationship between tables
Ensures referential integrity, foreign key value exists in referenced table
What are composite keys
Two or more columns used together as primary key
Useful when single column not unique enough to serve as primary key
Ensure combination of values in specified column is unique
Enhances data accuracy, prevents duplicate records with same combination of values
What are insert anomalies in databases
Certain data cannot be inserted into database, without presence of other data
Incomplete data entry, causes need for unnecessary dummy data
Solution: Normalise database to separate related data to different tables
Example: Trying to add a new course without enrolling any student
What are update anomalies in databases
Data requires multiple updates to avoid inconsistencies
Risk of data inconsistencies, increased maintenance effort
Solution: Normalise database to ensure each piece of data stored in only 1 place
Example: Updating a student’s address in multiple tables
What are delete anomalies in databases
Deleting data removes additional data that should be retained
Loss of valuable data, difficulty maintaining historical records
Solution: Implement foreign key constraints to prevent accidental deletions
Example: Deleting a student removes record of their enrolments
What is the purpose of data documentation for developers
Ensures Consistency: Provides clear & consistent reference for data structures, reducing errors & misunderstandings during development
Facilitates Maintenance: Helps developer understand database structure & relationship, easier to update / modify database in future
Supports Collaboration: Allows multiple developers to work on same project, shared understanding of database design & structure
Improves Troubleshooting: Helps identify & resolve issues related to data storage & retrieval efficiently
What are data dictionaries
Detailed description of all data elements in database, including names, types, formats, relationships
What are key components of data dictionaries
Table Names
Field Names
Data Types
Field Sizes
Constraints
What is purpose of data dictionaries
Helps developers understand database structure
Ensure consistent use of data across application
