Database Management Module Flashcards
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
What are ER diagrams
ER diagrams use symbols & notation to represent entities, attributes, relationships in database
Uses “crow’s feet” symbols to indicate relationships (One-To-Many, Many-To-Many)
What is purpose of ER diagrams
Provides clear & organised view of database structure, making it easier to design & understand relationships between entities
How do you analyse ER diagrams when in Crow’s Foot Notation
Look for entities relevant to application
Ensure relationships correctly represented (Eg. One player has many scores, but each score only linked to one game)
All necessary attributes included, accurately represented for each entity
How do you create accurate ER diagrams using Crow’s Foot Notation
Clearly define entities needed
Establish relationships between entities (Eg. One-To-One, One-To-Many)
Use crow’s foot symbols
Ensure diagram accurate reflects database structure
How do you create a data dictionary
Document all fields
Include types, sizes, constraints, for each table
What is resolving many to many relationships
Break down many-to-many relationships into two one-to-many relationships using junction tables
Unresolved M relationships lead to data redundancy and anomalies
Benefits:
- Ensures data integrity, simplifies database structure
What is Currency in factors that influence integrity of data
How up-to-date the data is within the game
Ensures all game elements reflect the most current state
Outdated data leads to discrepancies, such as incorrect resource counts or character abilities
What is Authenticity in factors that influence integrity of data
Data being used is genuine and trustworthy
Prevents cheating, maintains a fair playing environment
Use validation methods such as checksums or digital signatures
What is Relevance of Data in factors that influence integrity of data
Only data that serves purpose within game is used
Irrelevant data clutters game processes, leads to slower performance and crashes
Consistently audit game data to ensure it remains relevant to gameplay mechanics
What is Accuracy of Data in factors that influence integrity of data
Data correctly represents real-world or in-game values it’s supposed to
Accurate data crucial for balanced gameplay and fair outcomes
Inaccuracies lead to overpowered/underpowered elements within the game
What is Handling Outliers and Cleaning Data in Game in factors that influence integrity of data
Outliers are data points that differ significantly from other data, potentially due to errors
- Can distort game balance and player experience
Regularly check for and remove outliers / errors to maintain data integrity
What is the relationship between validity and accuracy of data
Data validity ensure that data conforms to expected format and logic
Accuracy means data is correct, validity means data fits the required criteria
- Data point can be valid but not accurate (Vice versa)
Implement validation checks, and accuracy tests, to ensure data is both valid and accurate
What is the purpose of 3NFing
Eliminate data redundancy, ensure data consistency
Done by resolving Many to Many relationships
How do you normalise to 1NF
Each table cell only contains atomic values, each record is unique, no repeating groups or arrays within column
Eg. Table with list of games that players own, each game in separate row
How do you normalise to 2NF
Ensure all non-key attributes depend on entire primary key
- Separate into two tables
Eg. Each game in player’s library should be linked to a specific player, not just a general game list
How do you normalise to 3NF
Remove attributes that do not directly depend on primary key
- Separate into two tables
Separate player information from game information
What is the final result after 3NF
Tables organised into relations, minimal redundancy
