Podcast
Questions and Answers
What is the first step in creating a physical database schema?
What is the first step in creating a physical database schema?
What is the purpose of domain integrity in database design?
What is the purpose of domain integrity in database design?
What is the type of database distribution where the entire database is located on a single server in one physical location?
What is the type of database distribution where the entire database is located on a single server in one physical location?
What is the term for the assurance that a foreign key value in one table has a matching primary key value in the related table?
What is the term for the assurance that a foreign key value in one table has a matching primary key value in the related table?
Signup and view all the answers
What is the purpose of creating an index for each primary and secondary key?
What is the purpose of creating an index for each primary and secondary key?
Signup and view all the answers
What is the term for the process of assigning tables or rows to different database servers or locations?
What is the term for the process of assigning tables or rows to different database servers or locations?
Signup and view all the answers
What is the purpose of defining data types, sizes, null settings, domains, and defaults for each attribute?
What is the purpose of defining data types, sizes, null settings, domains, and defaults for each attribute?
Signup and view all the answers
What is the type of database integrity that ensures that every table has a primary key?
What is the type of database integrity that ensures that every table has a primary key?
Signup and view all the answers
What is the purpose of evaluate and specifying referential integrity constraints?
What is the purpose of evaluate and specifying referential integrity constraints?
Signup and view all the answers
What is the term for the analysis of which business locations need access to which logical data entities and attributes?
What is the term for the analysis of which business locations need access to which logical data entities and attributes?
Signup and view all the answers
Study Notes
File and Table Structures
- Fixed-length record structures and variable-length record structures
- Blocking factor: the number of logical records included in a single read or write operation
Types of Files and Tables
- Master files: records that are relatively permanent, but values may change
- Transaction files: records that describe business events
- Document files: historical data for review without the overhead of regenerating documents
- Archival files: master and transaction records that have been deleted
- Table lookup files: relatively static data that can be shared to maintain consistency
- Audit files: special records of updates to other files
Data Architecture
- Definition of how files and databases are developed and used to store data
- Specification of file and/or database technology to be used
- Administrative structure set up to manage the data resource
Data Storage
- Data is stored in a combination of:
- Conventional files
- Operational databases (support day-to-day operations and transactions)
- Data warehouses (store data extracted from operational databases for data mining)
- Personal databases
- Work group databases
Data Administration
- Data administrator: responsible for data planning, definition, architecture, and management
- Database administrator: responsible for database technology, design, construction, security, backup, and recovery, and performance tuning
Database Architecture
- Database technology used to support data architecture
- Includes database engine, utilities, CASE tools, and development tools
- Database management system (DBMS): special software used to create, access, control, and manage a database
- Core of DBMS: database engine
- Data definition language (DDL): part of the engine used to physically define tables, fields, and structural relationships
- Data manipulation language (DML): part of the engine used to create, read, update, and delete records in the database, and navigate between different records
Relational Databases
- Implement stored data in a series of two-dimensional tables related to each other via foreign keys
- Physical data model: schema
- DDL and DML for a relational database: SQL (Structured Query Language)
- Triggers: programs embedded within a database that are automatically invoked by updates
- Stored procedures: programs embedded within a database that can be called from an application program
Database Design
- Goals:
- Efficient storage, update, and retrieval of data
- Reliable data with high integrity
- Adaptable and scalable to new and unforeseen requirements and applications
- Method for database design:
- Review logical data model
- Create a table for each entity
- Create fields for each attribute
- Create an index for each primary and secondary key
- Create an index for each subsetting criterion
- Designate foreign keys for relationships
- Define data types, sizes, null settings, domains, and defaults for each attribute
- Create or combine tables to implement supertype/subtype structures
- Evaluate and specify referential integrity constraints
Database Integrity
- Key integrity: every table should have a primary key
- Domain integrity: controls must be designed to ensure that no field takes on an inappropriate value
- Referential integrity: assurance that a foreign key value in one table has a matching primary key value in the related table
- Referential integrity constraints:
- No restriction
- Delete: cascade
- Delete: restrict
- Delete: set null
Database Distribution and Replication
- Data distribution analysis: determines which business locations need access to which logical data entities and attributes
- Centralization: entire database on a single server in one physical location
- Horizontal distribution (partitioning): tables or rows assigned to different database servers/locations
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
Description
Understand the different types of file structures, including fixed-length and variable-length record structures, and learn about blocking factor, master files, and transaction files.
