Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

CMPT 454 L1 Intro Exam Solutions: Relational Databases & DBMS, Exams of Database Management Systems (DBMS)

A comprehensive overview of relational database concepts and dbms fundamentals. It covers key topics such as relational models, schemas, constraints, sql queries, and dbms architecture. Exercises and complete solutions, making it a valuable resource for students studying database management systems.

Typology: Exams

2024/2025

Available from 02/14/2025

lyudmila-hanae
lyudmila-hanae 🇺🇸

1

(2)

7.8K documents

1 / 12

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
CMPT 454 L1 - Intro Exam With Complete Solutions 100% Verified
"Relational DB uses the _________ model: Entities and Relationships are represented by
___________, ___________"
relational, relations, tables
What needs to be determined to create a relational model of a real-world enterprise?
- Entities (students/courses)
- Relationships (student taking a course)
- Attributes (ID, name)
Within a table, ROWS represent _______ or _______
records, tuples
Are records unique?
Yes
A primary key can be made up of ______ or _______ ______________
one or more attributes
pf3
pf4
pf5
pf8
pf9
pfa

Partial preview of the text

Download CMPT 454 L1 Intro Exam Solutions: Relational Databases & DBMS and more Exams Database Management Systems (DBMS) in PDF only on Docsity!

CMPT 454 L1 - Intro Exam With Complete Solutions 100% Verified

"Relational DB uses the _________ model: Entities and Relationships are represented by ___________, ___________" relational, relations, tables

What needs to be determined to create a relational model of a real-world enterprise?

  • Entities (students/courses)
  • Relationships (student taking a course)
  • Attributes (ID, name)

Within a table, ROWS represent _______ or _______ records, tuples

Are records unique? Yes

A primary key can be made up of ______ or _______ ______________ one or more attributes

Within a table, each column represents an ___________. Do columns have to be unique? attribute

yes

Table is also considered an instance of a _____________ schema

Where are schemas defined? In the DB

Where is the schema stored? system catalog

Would Schemas be considered a part of the meta data? Yes

Schema is associated with what? One or more constraints

Multiple table queries generally entail what? Joins

or at least similar operation

Can joins be cheap processing? What 'O' algorithm are joins No, they are expensive

~ O(n²)

________ must determine which join algo to use in _____ ______ __________ DBMS any given query

______ facilitates access to DBs DBMS

An application uses a __________ _____ when interacting with a DB. Give examples of how information is related to schemas conceptual view

tables, columns, domains, etc

DBMS maps the ________________ view to the ________ view. conceptual, physical

Data is stored in ________ ________ or ________ _______ while using most efficient access methods, examples being... main memory or secondary storage

indexes

DBMS is responsible for ____________ data from ______ ___________ accessing data from storage devices

Why use a DBMS? To store data for:

  1. complex data model
  2. large data set

Additional issues a DBMS can handle are

  • Concurrency
  • Recovery
  • Security

DBMS is linked to application @ compile time (same address space)

Where are embedded DB applications often used? mobile systems

Tiered Client-Server application Application connects to DBMS via ODBC, JDBC, etc

May entail connecting to a server that connects to a DB server

Application and DBMS are considered combined or separate machines. separate

Through what does an Application and DBMS communicate? a network

In a tiered architectures, the different number of tiers can refer to

  • client interface
  • business logic
  • network communication
  • ...

Client-Server architecture is common with what type of DBMS? Enterprise DBMSs

Describe DBMS functionality Manages the following:

  1. Connections
  2. data manipulation language commands
  3. commands and generates query plan
  4. returning of results

What does it mean to manage data manipulation language commands

  • ctrl when to process a query
  • assigns threads to run commands

What does commands and generates query plan mean?

  • Using storage and transaction management code

Thrashing when OS spends more time swapping pages between memory and disk than executing processes.

This happens when the degree of multiprogramming, or the number of processes in memory, exceeds the available memory space

Throughput average # of units actually being processed

Relational Query Processor

  • Once accepted by process manager, query has sufficient resources to be evaluated
  • checks user authorization
  • generates query plan through a Rewriter and Optimizer
  • plan is executed by the Plan Executor

Plan Executor Using general-purpose ops (join, sort, select) to make calls to storage manager for data access

Transactional Storage Manager

  • Manages all data access and data manipulation
  • Key component for OLTP
  • Eff ways to read/modify data
  • Guarantees data integrity
  • Control returned to query processor

How does the Transactional Storage Manager Eff read/modify data indexes, table formats, buffering and careful use of storage media

How does the Transactional Storage Manager guarantee data integrity

  • Manages concurrent access
  • Allows for recovery from failures and crashes

Connection Manager Ships the generated results back

How do different DBMSs differ