CSC460 - Database Design Final Exam Study
Set With Complete Solutions
Database - ANSWER Main ideas:
Collection of (Inter) Related Data/Records
Other key ideas:
For Enterprise/Organization/Common Purpose
For Sharing
For Minimized Redundancy
For Persistence
Cell phone contact list is a database. Wallet is a database.
What does DBMS stand for? - ANSWER Database Management System
What is a DBMS or Database Management System? - ANSWER A software
system/computerized record-keeping system/collection of programs/program
product/set of programs to enable users to create, maintain, and manage data
What are the components of the DBMS? - ANSWER The Database
Administration (Database Administrator/DBA to handle
maintenance/performance/security...)
Application Programs (for queries and interacting with data)
Hardware (often a dedicated cluster of servers)
What are the advantages of a DBMS? - ANSWER Data Sharing
...one common location for data helps maintain data consistency
Redundancy Control
...Data Normalization handles intra-file redundancy
Centralized Control
...DBAs provide experienced oversight while allowing users to stay users
Data Integrity
...DBMSs support user-defined integrity rules
Data Security
...DBMS can flexibly limit access to users
Management of types of data views
...automatically manages presentations of data for clarity/convenience/security
,Data Independence:
- Logical
...Difficult to achieve, allows schema to change without applications changing
- Physical
...Allows physical storage to change without applications changing
What are the disadvantages of a DBMS? - ANSWER Design is complex
Cost
...licenses are expensive, so are admins
Size (it's a large piece of software, needs a lot of memory to run well)
Cost (Software licenses, maintenance people, servers, additional hardware,
conversion...)
Performance (if it's generalized, performance may not be good)
Greater impact of failure
What are the types of Data Languages? - ANSWER Query Language (Read-only,
just answers questions)
Data Description Language/DDL (turns conceptual schema into a physical
database description)
Data Manipulation Language/DML (insertion/deletion of data)
Data Control Language/DCL (handles security)
What is a schema? - ANSWER The description of the structure of the database -
what tables does it consist of? What are the attributes? Various granularities.
What is architecture? - ANSWER A total description of the DBMS components
and how they interconnect - includes the schema, hardware, etc.
What is SPARC? - ANSWER Standards Planning and Requirements Committee
What is ANSI? - ANSWER American National Standards Institute
What were the goals of ANSI/SPARC architecture? - ANSWER Allow for multiple
user views of data
Allow for disk-level description of data
Provide an abstraction layer to separate the two
What does ANSI/SPARC architecture look like? - ANSWER External level views
mapped to each other and the conceptual level, mapped to internal level,
mapped to DB
External communicates with user
Conceptual defines field groupings/relationships, etc.
Internal is record sizes, indices, record ordering, low-level metadata
, Why did DBMSs move away from centralized architecture? - ANSWER All
components being on one computer created performance bottlenecks, and
computers soon became less expensive and more powerful
What does two-tier architecture consist of? - ANSWER The client - user side
The server - runs DBMS and fields requests
What might a four-tier architecture consist of? - ANSWER Client, web server,
application server, DBMS connected to DB
Why are single DBMS servers bad and distributed DBMSs good? - ANSWER A
single server is a single point of failure - if one goes, everything goes.
Distributing allows for some, all, or none of the DB to be stored locally, and
spreads things out to multiple failure points.
What constitutes primary storage? - ANSWER CPU Registers, Cache, Main
Memory (RAM)
Is primary storage volatile or non-volatile? - ANSWER volatile
Are CPU registers primary, secondary, or tertiary storage? - ANSWER Primary
Is cache primary, secondary, or tertiary storage? - ANSWER Primary
Is RAM primary, secondary, or tertiary storage? - ANSWER Primary
Is secondary storage volatile or non-volatile? - ANSWER non-volatile
Is tertiary storage volatile or non-volatile? - ANSWER non-volatile
Is SSD primary, secondary, or tertiary storage? - ANSWER Secondary
Is HDD primary, secondary, or tertiary storage? - ANSWER Secondary
Is tape/optical storage primary, secondary, or tertiary storage? - ANSWER
Tertiary
What constitutes secondary storage? - ANSWER SSD and HDD
What constitutes tertiary storage? - ANSWER Tape/optical storage
What are the parts of the Hard Drive? - ANSWER An axle holding platters with
two surfaces, a Read/Write arm, and a Read/Write head
Set With Complete Solutions
Database - ANSWER Main ideas:
Collection of (Inter) Related Data/Records
Other key ideas:
For Enterprise/Organization/Common Purpose
For Sharing
For Minimized Redundancy
For Persistence
Cell phone contact list is a database. Wallet is a database.
What does DBMS stand for? - ANSWER Database Management System
What is a DBMS or Database Management System? - ANSWER A software
system/computerized record-keeping system/collection of programs/program
product/set of programs to enable users to create, maintain, and manage data
What are the components of the DBMS? - ANSWER The Database
Administration (Database Administrator/DBA to handle
maintenance/performance/security...)
Application Programs (for queries and interacting with data)
Hardware (often a dedicated cluster of servers)
What are the advantages of a DBMS? - ANSWER Data Sharing
...one common location for data helps maintain data consistency
Redundancy Control
...Data Normalization handles intra-file redundancy
Centralized Control
...DBAs provide experienced oversight while allowing users to stay users
Data Integrity
...DBMSs support user-defined integrity rules
Data Security
...DBMS can flexibly limit access to users
Management of types of data views
...automatically manages presentations of data for clarity/convenience/security
,Data Independence:
- Logical
...Difficult to achieve, allows schema to change without applications changing
- Physical
...Allows physical storage to change without applications changing
What are the disadvantages of a DBMS? - ANSWER Design is complex
Cost
...licenses are expensive, so are admins
Size (it's a large piece of software, needs a lot of memory to run well)
Cost (Software licenses, maintenance people, servers, additional hardware,
conversion...)
Performance (if it's generalized, performance may not be good)
Greater impact of failure
What are the types of Data Languages? - ANSWER Query Language (Read-only,
just answers questions)
Data Description Language/DDL (turns conceptual schema into a physical
database description)
Data Manipulation Language/DML (insertion/deletion of data)
Data Control Language/DCL (handles security)
What is a schema? - ANSWER The description of the structure of the database -
what tables does it consist of? What are the attributes? Various granularities.
What is architecture? - ANSWER A total description of the DBMS components
and how they interconnect - includes the schema, hardware, etc.
What is SPARC? - ANSWER Standards Planning and Requirements Committee
What is ANSI? - ANSWER American National Standards Institute
What were the goals of ANSI/SPARC architecture? - ANSWER Allow for multiple
user views of data
Allow for disk-level description of data
Provide an abstraction layer to separate the two
What does ANSI/SPARC architecture look like? - ANSWER External level views
mapped to each other and the conceptual level, mapped to internal level,
mapped to DB
External communicates with user
Conceptual defines field groupings/relationships, etc.
Internal is record sizes, indices, record ordering, low-level metadata
, Why did DBMSs move away from centralized architecture? - ANSWER All
components being on one computer created performance bottlenecks, and
computers soon became less expensive and more powerful
What does two-tier architecture consist of? - ANSWER The client - user side
The server - runs DBMS and fields requests
What might a four-tier architecture consist of? - ANSWER Client, web server,
application server, DBMS connected to DB
Why are single DBMS servers bad and distributed DBMSs good? - ANSWER A
single server is a single point of failure - if one goes, everything goes.
Distributing allows for some, all, or none of the DB to be stored locally, and
spreads things out to multiple failure points.
What constitutes primary storage? - ANSWER CPU Registers, Cache, Main
Memory (RAM)
Is primary storage volatile or non-volatile? - ANSWER volatile
Are CPU registers primary, secondary, or tertiary storage? - ANSWER Primary
Is cache primary, secondary, or tertiary storage? - ANSWER Primary
Is RAM primary, secondary, or tertiary storage? - ANSWER Primary
Is secondary storage volatile or non-volatile? - ANSWER non-volatile
Is tertiary storage volatile or non-volatile? - ANSWER non-volatile
Is SSD primary, secondary, or tertiary storage? - ANSWER Secondary
Is HDD primary, secondary, or tertiary storage? - ANSWER Secondary
Is tape/optical storage primary, secondary, or tertiary storage? - ANSWER
Tertiary
What constitutes secondary storage? - ANSWER SSD and HDD
What constitutes tertiary storage? - ANSWER Tape/optical storage
What are the parts of the Hard Drive? - ANSWER An axle holding platters with
two surfaces, a Read/Write arm, and a Read/Write head