Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

dbs > ch2 > Flashcards

ch2 Flashcards

1
Q

Define: data model

A

a collection of concepts that can be used to describe the structure of a database [structure of a database = the data types, relationships, and constraints that apply to the data];
most data models also include a set of basic operations for specifying retrievals and updates on the DB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Define: database schema

A

the description of a database; specified during design; not expected to change frequently

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Define: database state

A

AKA snapshot; the data in the DB at a particular moment in time; AKA the current set of occurrences or instances in the DB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Define: internal schema

A

at the internal level; lowest level of three-schema architecture;
describes the physical storage structure of the DB;
uses a physical data model;
describes the complete details of data storage and access paths for the DB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Define: conceptual schema

A

at the conceptual level; middle level of three-schema architecture;
describes the structure of the whole DB for a community of users;
described by a conceptual data model;
hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints;

This implementation conceptual schema is often based on a conceptual schema design in a high-level data model.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Define: external schema

A

at the view level AKA external level
AKA user views; top level of three-schema architecture;

The external or view level includes a number of external schemas or user views.

each external schema:

  • describes the part of the DB that a particular user group is interested in and hides the rest of the DB from that user group;
  • is typically implemented using a representational data model, possibly based on an external schema design in a high-level conceptual data model.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Define: data independence

A

the capacity to change the schema at one level of a DB system without having to change the schema at the next higher level;

2 types:

  • logical data independence:
    • the capacity to change the conceptual schema without having to change the external schema or app programs
    • Only the view definition and the mappings need to be changed in a DBMS that supports logical data independence. After the conceptual schema undergoes a logical reorganization, application programs that reference the external schema constructs must work as before. Changes to constraints can be applied to the conceptual schema without affecting the external schemas or application programs.
  • physical data independence:
    • the capacity to change the internal schema without having to change the conceptual schema; hence the external schemas need not be changed as well
    • If the same data as before remains in the DB, we should not have to change the conceptual schema.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Define: DDL

A

Data Definition Language;

  • in DBMSs where NO strict separation btwn arch. levels is maintained: DDL is used by the DBA and DB designers to define conceptual and internal schemas
  • in DBMSs WITH clear separation btwn arch. levels is maintained: DDL is used to specify the conceptual schema only; can also be used to specify mappings btwn internal and conceptual schemas
  • in most DBMSs the DDL is used to define both the conceptual and external schemas (instead of a VDL)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Define: DML

A

Data Manipulation Language;
(once DB schemas are compiled and the DB is populated with data) DML is used to perform manipulations of the data in the DB, such as:
retrieval, insertion, deletion, and modification of the data

The DBMS provides a set of operations or a language called the data manipulation language (DML) for these purposes.

2 main types of DMLs:

  • high-level/nonprocedural DMLs:
    • AKA set-at-a-time DMLs, AKA set-oriented DMs, AKA a declarative language
    • can be used on its own to specify complex DB ops concisely; specifies WHICH data to retrieve rather than HOW to retrieve it
  • low-level/procedural DMLs:
    • AKA record-at-a-time DMLs
    • MUST be embedded in a general purpose programming language; typically retrieves individual records or objects from the DB and processes each separately
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Define: SDL

A

Storage Definition Language;
in DBMSs WITH clear separation btwn arch. levels is maintained: SDL is used to specify internal schema; can also be used to specify mappings btwn internal and conceptual schemas;
in relational DBMSs today, there is no specific language that performs the role of the SDL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Define: VDL

A

View Definition Language;
in true three-tier architecture, VDL would be used to specify user views and their mappings to the conceptual schema (but in most DBMSs the DDL is used to define both the conceptual and external schemas)
in relational DBMSs, SQL is used in the role of VDL to define user or application views as a result of predefined queries

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Define: query language

A

a high-level DML used in a standalone interactive manner;
in general, both retrieval and update commands of a high-level DML may be used interactively and are hence considered part of the query language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Define: host language

A

whenever DML commands, whether at a high level or low level, are embedded in a general-purpose programming language, that language is called the host language and the DML is called the data sublanguage

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Define: data sublanguage

A

whenever DML commands, whether at a high level or low level, are embedded in a general-purpose programming language, that language is called the host language and the DML is called the data sublanguage

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Define: database utility

A

part of a DBMS that helps the DBA manage the DB system;
common functionalities of utilities:
* Loading utility [load existing files into the DB]
* Backup utility
* Database storage reorganization utility
* Performance monitoring utilitiy [monitors performance and usage]
* others:
** sorting files
** handling data compression
** monitoring access by users
** interfacing with the network
** performing other functions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Define: catalog

A

[DBMS catalog?? AKA system catalog]

  • holds the meta-data (descriptions of the schemas)
  • [in the case of multiple-level DBMS:] can also hold mapping information between the various levels
  • includes info such as the names and sizes of files, names and data types of data items, storage details of each files, mapping info among schemas, and constraints
  • also stores many other types of info that are needed by the DBMS modules, which can then look up the catalog info as needed

[generic catalog??]
* “Three of the four important characteristics of the database approach, listed in Section 1.3, are (1) use of a catalog to store the database description (schema) so as to make it self-describing, (2) insulation of programs and data (program-data and program-operation independence), and (3), support of multiple user views.”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Define: client/server architecture

A

an architectural framework where a large number of computing environments are connected via a network;

  • defines specialized servers with specific functionalities;
  • client machines provide the user with appropriate interfaces to utilize these servers, as well as with the local processing power to run local applications
  • the resources provided by specialized servers can be accessed by many client machines
18
Q

Define: three-tier architecture

A

a three-tiered client/server architecture:

  • client tier: AKA client, AKA presentation layer, AKA user interface
    • contains UIs and web browsers
  • middle tier: AKA application server, AKA web server, AKA business logic layer, AKA application rules
    • runs app programs and stores business rules; accepts requests from clients, processes requests and sends DB queries and commands to the DB server, then acts as a conduit for passing partially processed data from the DB server to the clients
  • server tier: AKA database server, AKA database services layer, AKA data access
    • includes all data management services
19
Q

Define: n-tier architecture

A

divide up the layers between the user and the stored data into finer components;
n may be four or five tiers;
typically the business logic layer is divided into multiple layers;
distributes programming and data throughout a network;
another advantage; any one tier can run on an appropriate processor or OS platform and handled independently

20
Q

What concepts do high-level or conceptual data models use?

A

concepts that are close to the way many users perceive data;

concepts such as entities, attributes, and relationships

21
Q

How are they [high-level or conceptual data models] different from low-level or physical models?

A

low-level/physical models:

  • provide concepts that describe how data is stored on the computer storage media, typically magnetic disks;
  • describe how data is stored as files in the computer by representing information such as record formats, record orderings, and access paths

concepts provided by physical data models are generally meant for computer specialists, not for end users

22
Q

What is the difference between a database schema and a database state?

A
  • DB schema: a description of the database; specified during data design and not expected to change frequently; AKA “intension”
  • DB state: the data in the DB at a particular moment in time; may change quite frequently; AKA “extension”
  • when we define a new DB, we specify the DB schema only; at this point the corresponding DB state is the empty state with no data
  • when we first populate or load the initial data into the DB we get the initial state
  • from then on, any time an update operation is applied to the DB, we get another DB state
  • at any given point in time, the DB has a current state
  • DBMS is partially responsible for ensuring that every state of the DB is a valid state: i.e. a state that satisfies the structure and constraints specified in the schema
23
Q

Describe the three-schema architecture.

A

AKA ANSI/SPARC arch;

  • proposed to help achieve and validate three of the four important characteristics of the DB approach:
    1) use of a catalog to store the DB description (schema) so as to make it self-describing;
    2) insulation of programs and data (program-data and program-operation independence);
    3) support of multiple user views;
  • The three-level ANSI[/SPARC] architecture has an important place in database technology development because it clearly separates the users’ external level, the database’s conceptual level, and the internal storage level for designing a database.

internal schema:
at the internal level; lowest level of three-schema architecture;
describes the physical storage structure of the DB;
uses a physical data model;
describes the complete details of data storage and access paths for the DB

conceptual schema:
at the conceptual level; middle level of three-schema architecture;
describes the structure of the whole DB for a community of users;
described by a conceptual data model;
hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints;

This implementation conceptual schema is often based on a conceptual schema design in a high-level data model.

external schemas:
at the view level AKA external level
AKA user views; top level of three-schema architecture;

The external or view level includes a number of external schemas or user views.

each external schema:

  • describes the part of the DB that a particular user group is interested in and hides the rest of the DB from that user group;
  • is typically implemented using a representational data model, possibly based on an external schema design in a high-level conceptual data model.
24
Q

[three-schema architecture] Why do we need mappings among schema levels?

A

the actual data is only stored at the physical level; each user-group refers to its own external schema:

  • so the DBMS has to transform a request specified on an external schema into a request specified against the conceptual schema, and then into a request on the internal schema for processing over the stored DB;
  • if the request is a retrieval, then the DBMS has to reformat the result data extracted from the stored DB into the user’s external view
25
Q

[three-schema architecture] How do different schema definition languages support this architecture?

A

[schema definition languages???]

  • in many DBMSs where no strict separation btwn conceptual and internal schemas is maintained: the DBA and DB designers use the DDL to define both schemas; the DBMS will have a DDL compiler whose function is to process DDL statements in order to identify descriptions of the schema constructs and to store the schema description in the DBMS catalog
  • in DBMSs where there is a clear separation btwn conceptual and internal schemas: the DDL is used to specify the conceptual schema and the S[TORAGE]DL is used to specify the internal schema; the mappings btwn the 2 can be done in either language;
    • in most DBMSs today, there is no specific language that performs the role of the SDL; rather the internal schema is specified by a combo of functions, parameters, and specifications related to storage of files; these permit the DBA staff to control indexing choices and mapping of data to storage
26
Q

What is the difference between logical data independence and physical data independence?

A
  • logical data independence:
    • the capacity to change the conceptual schema without having to change the external schema or app programs
    • Only the view definition and the mappings need to be changed in a DBMS that supports logical data independence. After the conceptual schema undergoes a logical reorganization, application programs that reference the external schema constructs must work as before. Changes to constraints can be applied to the conceptual schema without affecting the external schemas or application programs.
  • physical data independence:
    • the capacity to change the internal schema without having to change the conceptual schema; hence the external schemas need not be changed as well
    • If the same data as before remains in the DB, we should not have to change the conceptual schema.
27
Q

[logical data independence vs physical data independence] Which one is harder to achieve? Why?

A

logical data independence is harder to achieve because it allows structural and constraint changes without affecting application programs, which is a much stricter requirement

28
Q

Why are low-level DMLs also called record-at-a-time DMLs and high-level DMLS are called set-at-a-time DMLs?

A

low-level DMLs are also called record-at-a-time DMLs because they typically retrieve individual records or objects from the DB and process each separately, so they need to use programming language constructs such as looping to retrieve and process each record from a set of records

high-level DMLs are also called set-at-a-time DMLs (or set-oriented DMLs) because they can specify and retrieve many records in a single DML statement; a query in a high-level DML often specifies WHICH data to retrieve rather than HOW to retrieve it, which is why they [these languages - DMLs] are also called declarative

29
Q

How are forms-based interfaces different from the menu-based interfaces?

A

forms-based interfaces:

  • displays a form to each user
  • users can fill out the form entries to insert new data, or they can fill out only certain entries in which case the DBMS will retrieve matching data for the remaining entries
  • usually designed and programmed for naive users as interfaces to canned transactions

menu-based interfaces:

  • present the user with a list of options (called menus) that lead the user through the formulation of a request
  • do away with the need to memorize the specific commands and syntax of a query language; rather the query is composed step-by-step by picking options from a menu that is displayed by the system

HM thoughts: form-based interfaces are probably potentially more error-prone, depending on how open-ended the form fields are

30
Q

Discuss the role of the precompiler in a DBMS environment.

A

precompilers are part of the interface for application programmers;

  • app programmers write their programs in host languages like java or C; then they submit them to the pre-compiler;
  • the precompiler extracts the DML commands and sends them to the DML compiler to be compiled into object code for DB access
  • the rest of the program is sent to the host language compiler
  • the object code for the DML commands and the rest of the program are linked, which forms a canned transaction whose executable code includes calls to the runtime DB processor
31
Q

What is the difference between the two-tier and three-tier client/server architectures?

A

the three-tiered architecture has an intermediate layer between the client and DB server layers; this middle tier is the app server or web server and runs application programs and stores business rules (procedures or constraints) that are used to access data from the DB server; it can also improve security by checking a client’s credentials before forwarding a request on to the DB server
+security

the two-tiered architecture is only split into client and server levels; the UI programs and app programs run on the client side; the query and transaction processing (i.e. the DBMS) runs on the server side; when the client needs DB access, the program establishes a connection to the DBMS, then once the connection is created the client program can communicate with the DBMS
+ simplicity
+ seamless compatibility with existing systems

32
Q

What is the use of a data dictionary in a database system?

A

a data dictionary is similar to the DBMS catalog but it includes a wider variety of information and is accessed mainly by users rather than by the DBMS software;
in large organizations, an expanded data dictionary (AKA data repository, AKA information repository) can be helpful;
because it stores catalog info about schemas and constraints AND other info such as design decisions, usage standards, app program descriptions, and user info;
it can be accessed DIRECTLY by users or the DBA when needed

33
Q

What is the additional functionality incorporated in n-tier architecture (n > 3)?

A

typically the business logic layer is divided into multiple layers;
it distributes programming and data throughout a network;
another advantage: any one tier can run on an appropriate processor or OS platform and handled independently

34
Q

[Think of different users for the database shown in Figure 1.2.] What types of applications would each user need?

A
  • student: get semester schedule; get list of grades; get list of courses offered during given semester; get pre-requisites for a given course; update current enrollments (during registration: add/drop a section)
  • instructor: get number of sections [total/for a given course]; get current courses; get current students in a section; get/set students grades
  • department head: get/set list of courses; get/set list of sections; get/set students in each section/course; get/set list of courses and prereqs
  • academic advisor: get/set student enrollments; get student grades; get list of courses, sections
35
Q

[Think of different users for the database shown in Figure 1.2.] To which user category would each belong, and what type of interface would each need?

A
  • student: naive/parametric end users; menu-based/GUI-based/forms-based/keyword-based DB search/mobile apps
  • instructor: naive/parametric end users; GUI-based/forms-based/mobile apps
  • department head: casual end users; menu-based/GUI-based/forms-based/keyword-based DB search
  • academic advisor: naive/parametric end users; menu-based/GUI-based/forms-based/keyword-based DB search
36
Q

Choose a database application with which you are familiar. Design a schema and show a sample database for that application, using the notation of Figures 1.2 and 2.1.

A

.

37
Q

[Choose a database application with which you are familiar. Design a schema and show a sample database for that application, using the notation of Figures 1.2 and 2.1.]
What types of additional information and constraints would you like to represent in the schema?

A

.

38
Q

[Choose a database application with which you are familiar. Design a schema and show a sample database for that application, using the notation of Figures 1.2 and 2.1.]
Think of several users of your database and design a view for each.

A

.

39
Q

How is DBMS classified on the basis of number of sites over which the database is distributed?
Give an example of each.

A
  • centralized: data is stored on a single computer site; fun little side-project app on home computer
  • distributed: data/DBMS software is stored over multiple computer sites connected by a network
    • homogeneous: all sites use the same DBMS software; work - sapling: replicated DBs on different servers to improve fault tolerance and performance (speed)
    • heterogeneous: different sites can use different DBMS software; work - flipit: flipit LMS DB in peer1 MSSQLserver vs flipit payment DB in AWS mongoDB
40
Q

Consider the following database schema. In the Book_tbl table, the ISBN column must be unique to provide a guarantee uniqueness for a specific column (to prevent two books from having the same ISBN). Identify the column or the group of columns in the other tables that must be unique across all rows in the table.

Book_tbl:
|| ISBN || BookName || Price || PublishingYear ||

Publisher_tbl:
|| Publisher_ID || PublisherName || EmailID || Phone ||

Author_tbl:
|| Author_ID || AuthorName || EmailID || Phone ||

A
  • Book_tbl: ISBN
  • Publisher_tbl: Publisher_ID (assuming that this means an individual publisher person’s ID), EmailID, (not going to say Phone bc maybe you have multiple publishers at the same location thus you have same phone # for all)
  • Author_tbl: Author_ID, EmailID, (not going to say Phone bc maybe you have multiple authors at the same location thus you have same phone # for all)

dbs (9 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions