Flashcards
Database Mangement
Cards in this deck (200)
- 1Sources of information for physical design process includeslogical data model and documentation that describes model.
- 2Logical database design is concerned with thewhat, physical database design is concerned with the how
- 3Physical Database DesignProcess of producing a description of the implementation of the database on secondary storage
- 4Physical Database Design describes thebase relations, file organizations, and indexes used to achieve efficient access to the data, and any associated integrity constraints and security measures
- 5Overview of Physical Database Design Methodology Step 3Translate logical data model for target DBMS
- 6Overview of Physical Database Design Methodology Step 3.1Design base relations
- 7Overview of Physical Database Design Methodology Step 3.2Design representation of derived data
- 8Overview of Physical Database Design Methodology Step 3.3Design general constraints
- 9Overview of Physical Database Design Methodology Step 4Design file organizations and indexes
- 10Overview of Physical Database Design Methodology Step 4.1Analyze transactions
- 11Overview of Physical Database Design Methodology Step 4.2Choose file organizations
- 12Overview of Physical Database Design Methodology Step 4.3Choose indexes
- 13Overview of Physical Database Design Methodology Step 4.4Estimate disk space requirements
- 14Overview of Physical Database Design Methodology Step 5Design user views
- 15Overview of Physical Database Design Methodology Step 6Design security mechanisms
- 16Overview of Physical Database Design Methodology Step 7Consider the introduction of controlled redundancy
- 17Overview of Physical Database Design Methodology Step 8Monitor and tune operational system
- 18Physical Database Design Methodology Step 3 Translate Logical Data Model for Target DBMSTo produce a relational database schema from the logical data model that can be implemented in the target DBMS.
Need to know functionality of target DBMS such as how to create base relations and whether the system supports the definition of:
PKs, FKs, and AKs;
required data – i.e. whether system supports NOT NULL;
domains;
relational integrity constraints;
general constraints. - 19Step 3.1 Design base relations To decide how to represent base relations identified in logical model in target DBMS. For each relation, need to definethe name of the relation;
a list of simple attributes in brackets;
the PK and, where appropriate, AKs and FKs.
referential integrity constraints for any FKs identified. - 20Step 3.1 Design base relations From data dictionary, we have for each attribute:its domain, consisting of a data type, length, and any constraints on the domain;
an optional default value for the attribute;
whether it can hold nulls;
whether it is derived, and if so, how it should be computed. - 21Step 3.2 Design representation of derived data decide how to represent any derived data present in logical data model in target DBMS.Examine logical data model and data dictionary, and produce list of all derived attributes.
Derived attribute can be stored in database or calculated every time it is needed.
Option selected is based on:
additional cost to store the derived data and keep it consistent with operational data from which it is derived;
cost to calculate it each time it is required.
Less expensive option is chosen subject to performance constraints. - 22Step 3.3 Design general constraints To design the general constraints for target DBMS.Some DBMS provide more facilities than others for defining enterprise constraints. Example
CONSTRAINT StaffNotHandlingTooMuch
CHECK (NOT EXISTS (SELECT staffNo
FROM PropertyForRent
GROUP BY staffNo
HAVING COUNT(*) > 100)) - 23Step 4 Design File Organizations and Indexes 1.To determine... 2.Must understand the....1. optimal file organizations to store the base relations and the indexes that are required to achieve acceptable performance; that is, the way in which relations and tuples will be held on secondary storage.
2. Must understand the typical workload that database must support. - 24Step 4.1 Analyze transactions 1. To understand the functionality of the ...... 2. Attempt to identify performance criteria, such as......2. To understand the functionality of the transactions that will run on the database and to analyze the important transactions.
1.transactions that run frequently and will have a significant impact on performance;
transactions that are critical to the business;
times during the day/week when there will be a high demand made on the database (called the peak load). - 25Step 4.1 Analyze transactions 1.Use this information to identify the... 2.Also need to know high-level....1. Use this information to identify the parts of the database that may cause performance problems.
2.Also need to know high-level functionality of the transactions, such as:
attributes that are updated;
search criteria used in a query. - 26Step 4.1 Analyze transactions 1.Often not possible to analyze... 2.To help identify these can use....1. Often not possible to analyze all transactions, so investigate most ‘important’ ones.
2. To help identify these can use:
transaction/relation cross-reference matrix, showing relations that each transaction accesses, and/or
transaction usage map, indicating which relations are potentially heavily used - 27Step 4.1 Analyze transactions To focus on areas that may be problematic (1) Map all... (2) Determine which... (3) Analyze the data...(1) Map all transaction paths to relations.
(2) Determine which relations are most frequently accessed by transactions.
(3) Analyze the data usage of selected transactions that involve these relations. - 28Step 4.2 Choose file organizations 1To determine an.... 2.File organizations include... 3.Some DBMSs may not allow...1. To determine an efficient file organization for each base relation.
2. File organizations include Heap, Hash, Indexed Sequential Access Method (ISAM), B+-Tree, and Clusters.
3. Some DBMSs may not allow selection of file organizations. - 29Step 4.3 Choose indexes 1.To determine whether... 2.One approach is to keep...To determine whether adding indexes will improve the performance of the system.
One approach is to keep tuples unordered and create as many secondary indexes as necessary. - 30Step 4.3 Choose indexes 1.Another approach is to... 2.In this case, choose the...Another approach is to order tuples in the relation by specifying a primary or clustering index.
In this case, choose the attribute for ordering or clustering the tuples as:
attribute that is used most often for join operations - this makes join operation more efficient, or
attribute that is used most often to access the tuples in a relation in order of that attribute. - 31Step 4.3 Choose indexes 1.f ordering attribute chosen is key of relation the index will be a.. 2..Each relation can only have either a... 3. Secondary indexes provide a mechanism for...1 index will be a primary index; otherwise, index will be a clustering index.
2.primary index or a clustering index.
3. specifying an additional key for a base relation that can be used to retrieve data more efficiently. - 32Step 4.3 Choose indexes Have to balance overhead involved in maintenance and use of secondary indexes against performance improvement gained when retrieving data. This includes: 1.adding an... 2.updating secondary index when 3. increase in.. 4.possible performance degradation during query optimization to1. index record to every secondary index whenever tuple is inserted;
2. corresponding tuple updated;
3. disk space needed to store secondary index;
4. consider all secondary indexes. - 33Step 4.3 Choose indexes – Guidelines for choosing ‘wish-list 1. Do not index.. 2. Index PK of a relation if it is 3. Add secondary index to a FK if 4. Add secondary index to any attribute heavily used as a.. 5. Add secondary index on attributes involved in:1. small relations.
2. not a key of the file organization.
3.it is frequently accessed.
4. secondary key.
5. selection or join criteria; ORDER BY; GROUP BY; and other operations involving sorting (such as UNION or DISTINCT). - 34Step 4.3 Choose indexes – Guidelines for choosing ‘wish-list 6. Add secondary index on attributes involved in.. 7. Add secondary index on attributes that.. 8. Avoid indexing an attribute or relation that is.. 9. Avoid indexing an attribute if the query will retrieve a.. 10. Avoid indexing attributes that consist of..6. on attributes involved in built-in functions.
7. could result in an index-only plan.
8 . frequently updated.
9. significant proportion of the relation.
10 . long character strings. - 35Step 4.4 Estimate disk space requirementsTo estimate the amount of disk space that will be required by the database.
- 36Step 5 Design User ViewsTo design the user views that were identified during the Requirements Collection and Analysis stage of the database system development lifecycle.
- 37Step 6 Design Security MeasuresTo design the security measures for the database as specified by the users.
- 38Purpose of NormalizationNormalizationis a technique for producing a set of suitable relations that support the data requirements of an enterprise.
- 39Purpose of Normalization Characteristics of a suitable set of relations include: 1. the minimal number of ..... necessary to support the data requirements of the enterprise. 2. attributes with a close logical relationship... 3. minimal....with each attribute ....1. attributes
2. are found in the same relation
3. redundancy with each attribute represented only once with the important exception of attributes that form all or part of foreign keys. - 40Potential benefits for implemented database include:Updates to the data stored in the database are achieved with a minimal number of operations thus reducing the opportunities for data inconsistencies.
Reduction in the file storage space required by the base relations thus minimizing costs. - 41Data Redundancy and Update Anomalies Relations that contain redundant information may potentially suffer from update anomalies. Types of update anomalies include:Insertion
Deletion
Modification - 42Lossless-join and Dependency Preservation Properties 1. Lossless-join property enables... 2. Dependency preservation property enables...1. us to find any instance of the original relation from corresponding instances in the smaller relations.
2. enables us to enforce a constraint on the original relation by enforcing some constraint on each of the smaller relations. - 43Functional Dependencies Important concept associated with normalization. Functional dependency describes ..relationship between attributes.
For example, if A and B are attributes of relation R, B is functionally dependent on A (denoted A B), if each value of A in R is associated with exactly one value of B in R. - 44Characteristics of Functional Dependencies Property of the meaning or semantics of the attributes in a relation. Diagrammatic representation.Diagrammatic representation
- 45Characteristics of Functional Dependencies The determinant of a functional dependency refers tothe attribute or group of attributes on the left-hand side of the arrow.
- 46An Example Functional Dependency
- 47Characteristics of Functional Dependencies Determinants should have the minimal This requirement is callednumber of attributes necessary to maintain the functional dependency with the attribute(s) on the right hand-side.
full functional dependency. - 48Characteristics of Functional Dependencies Full functional dependency indicates that if A and B are attributes of a relation,B is fully functionally dependent on A, if B is functionally dependent on A, but not on any proper subset of A.
- 49Example Full Functional Dependency Exists in the Staff relation (see Slide 12). staffNo, sName → branchNo True - each value However, branchNo is alsoof (staffNo, sName) is associated with a single value of branchNo.
functionally dependent on a subset of (staffNo, sName), namely staffNo. Example above is a partial dependency. - 50Characteristics of Functional Dependencies Main characteristics of full functional dependencies used in normalization: There is a one-to-one relationship between Holds for all time. The determinant has the minimal number ofthe attribute(s) on the left-hand side (determinant) and those on the right-hand side of a functional dependency.
attributes necessary to maintain the dependency with the attribute(s) on the right hand-side. - 51Transitive Dependencies Important to recognize a transitive dependency because Transitive dependency describes a condition where A, B, and C are attributes of a relation such thatits existence in a relation can potentially cause update anomalies.
if A → B and B → C, then C is transitively dependent on A via B (provided that A is not functionally dependent on B or C). - 52Consider functional dependencies in the StaffBranch relation (see Slide 12). staffNo → sName, position, salary, branchNo, bAddress branchNo → bAddress Transitive dependency branchNo → bAddress exists ...on staffNo via branchNo.
- 53The Process of Normalization Formal technique for analyzing a relation based on.. Often executed as a series of steps. Each step corresponds to ...its primary key and the functional dependencies between the attributes of that relation.
a specific normal form, which has known properties. - 54Examine semantics of attributes in StaffBranch relation (see Slide 12). Assume that position held and branch determine a member of staff’s salary. With sufficient information available, identify the functional dependencies for the StaffBranch relation as:staffNo → sName, position, salary, branchNo, bAddress
branchNo → bAddress
bAddress → branchNo
branchNo, position → salary
bAddress, position → salary
- 55Main purpose of identifying a set of functional dependencies for a relation is to ... An important integrity constraint to consider first is the...specify the set of integrity constraints that must hold on a relation.
identification of candidate keys, one of which is selected to be the primary key for the relation - 56StaffBranch relation has five functional dependencies (above). The determinants are.... To identify all candidate key(s), identify the attribute (or group of attributes) that uniquely identifies each tuple in this relation The only candidate key and therefore primary key for StaffBranch relation, isstaffNo, branchNo, bAddress, (branchNo, position), and (bAddress, position).
staffNo, as all other attributes of the relation are functionally dependent on staffNo.
- 57All attributes that are not part of a candidate key should befunctionally dependent on the key.
- 58Sample relation has four functional dependencies (see above). The determinants in the Sample relation are A, B, C, and (A, B). However, the only determinant that functionally determines all the other attributes of the relation is (A, B) is identified as the primary key for this relation.(A, B).
- 59As normalization proceeds, the relations become progressively more restricted (stronger) in format and also less ..vulnerable to update anomalies.
- 60Unnormalized Form (UNF) A table that .... To create an unnormalized table Transform the data from ....contains one or more repeating groups.
the information source (e.g. form) into table format with columns and rows. - 61First Normal Form (1NF) A relation in which theintersection of each row and column contains one and only one value.
- 62UNF to 1NF Nominate an attribute or group of attributes to Identify theact as the key for the unnormalized table.
repeating group(s) in the unnormalized table which repeats for the key attribute(s). - 63UNF to 1NF Remove the Or by Placing therepeating group by
Entering appropriate data into the empty columns of rows containing the repeating data (‘flattening’ the table).
repeating data along with a copy of the original key attribute(s) into a separate relation. - 64Second Normal Form (2NF) Based on the concept offull functional dependency.
- 65Full functional dependency indicates that ifA and B are attributes of a relation,
B is fully dependent on A if B is functionally dependent on A but not on any proper subset of A. - 66Second Normal Form (2NF) A relation that is in 1NF and everynon-primary-key attribute is fully functionally dependent on the primary key.
- 671NF to 2NF Identify the Identify the If partial dependencies exist on the primary keyprimary key for the 1NF relation.
functional dependencies in the relation.
remove them by placing then in a new relation along with a copy of their determinant. - 68Third Normal Form (3NF) Based on the concept of Transitive Dependency is a condition wheretransitive dependency.
A, B and C are attributes of a relation such that if A B and B C,
then C is transitively dependent on A through B. (Provided that A is not functionally dependent on B or C). - 69Third Normal Form (3NF) A relation that is in 1NF and 2NF and in whichno non-primary-key attribute is transitively dependent on the primary key.
- 702NF to 3NF Identify the Identify functional If transitive dependencies exist on the primary keyprimary key in the 2NF relation.
dependencies in the relation.
remove them by placing them in a new relation along with a copy of their dominant - 71General Definitions of 2NF and 3NF Second normal form (2NF) A relation that is in first normal form andevery non-primary-key attribute is fully functionally dependent on any candidate key.
- 72Third normal form (3NF) A relation that is in first and second normal form andin which no non-primary-key attribute is transitively dependent on any candidate key.
- 73Boyce–Codd normal form (or BCNF or 3.5NF) All redundancy based on functional dependency has beenremoved, although other types of redundancy may still exist
- 74Fourth Normal Form (4NF) Although BCNF removes anomalies due to functional dependencies, another type of dependency called amulti-valued dependency (MVD) can also cause data redundancy.
Possible existence of multi-valued dependencies in a relation is due to 1NF and can result in data redundancy. - 75Fourth Normal Form (4NF) Multi-valued Dependency (MVD) Dependency between attributes (for example, A, B, and C) in a relation, such that for each value of Athere is a set of values for B and a set of values for C. However, the set of values for B and C are independent of each other.
Not functional dependency - 76Fourth Normal Form (4NF) A multi-valued dependency can be further defined as being trivial or nontrivial. A MVD A −>> B in relation R is defined as being trivial if (a) B is a .... A MVD is defined as being nontrivial if neither (a) nor (b) are satisfied. A trivial MVD does not specify ...subset of A or (b) A B = R.
a constraint on a relation, while a nontrivial MVD does specify a constraint. - 77Defined as a relation that is in Boyce-Codd Normal Form and contains no nontrivial multi-valued dependencies.
- 78The Enhanced Entity-Relationship Model Semantic concepts are incorporated into the original ER model and called the Enhanced Entity-Relationship (EER) model. Examples of additional concept of EER model is calledspecialization / generalization
- 79Superclass An entity type that includes ' Subclass A distinct subgrouping ofone or more distinct subgroupings of its occurrences.
occurrences of an entity type. - 80Superclass/subclass relationship is Superclass may contain Not all members of a superclass need be aone-to-one (1:1).
overlapping or distinct subclasses.
member of a subclass. - 81Specialization / Generalization Specialization Process of........ differences between members of an entity by identifying their.....maximizing
distinguishing characteristics. - 82Generalization Process of........... differences between entities by identifying their....minimizing
common characteristics - 83Constraints on Specialization / Generalization Two constraints that may apply to a specialization/generalization: .participation constraints
disjoint constraints - 84Participation constraint Determines whetherevery member in superclass must participate as a member of a subclass.
May be mandatory or optional. - 85Constraints on Specialization / Generalization Disjoint constraint Describes relationship betweenmembers of the subclasses and indicates whether member of a superclass can be a member of one, or more than one, subclass.
- 86There are four categories of constraints of specialization and generalization:mandatory and disjoint
optional and disjoint
mandatory and nondisjoint
optional and nondisjoint.
May be implemented differently - 87Entity type Group of objects with Entity occurrencesame properties, identified by enterprise as having an independent existence.
Uniquely identifiable object of an entity type. - 88Relationship type Set of Relationship occurrence Uniquely identifiable association, which includesmeaningful associations among entity types.
one occurrence from each participating entity type. - 89Degree of a Relationship Relationship of degree : two is three is four is.Number of participating entities in relationship.
binary
ternary
quaternary - 90Recursive Relationship Relationship type where same entity typeparticipates more than once in different roles.
Relationships may be given role names to indicate purpose that each participating entity type plays in a relationship - 91Attribute Property of Attribute Domain Set ofan entity or a relationship type.
allowable values for one or more attributes. - 92Simple Attribute Attribute composed of Composite Attribute Attribute composeda single component with an independent existence.
of multiple components, each with an independent existence. - 93Single-valued Attribute Attribute that holds a Multi-valued Attribute Attribute that holdssingle value for each occurrence of an entity type.
multiple values for each occurrence of an entity type - 94Derived Attribute Attribute that represents a value that is derivable fromvalue of a related attribute, or set of attributes, not necessarily in the same entity type.
- 95Candidate Key Minimal set ofattributes that uniquely identifies each occurrence of an entity type.
- 96Primary Key Composite KeyCandidate key selected to uniquely identify each occurrence of an entity type.
A candidate key that consists of two or more attributes. - 97Strong Entity Type Entity type that is Weak Entity Type Entity type that isnot existence-dependent on some other entity type.
existence-dependent on some other entity type - 98Main type of constraint on relationships is calledmultiplicity
- 99Multiplicity -number (or range) of possible occurrences of an entity type that may relate to a single occurrence of an associated entity type through a particular relationship.
Represents policies (called business rules) established by user or company. - 100The most common degree for relationships isbinary.
- 101Binary relationships are generally referred to as being:one-to-one (1:1)
one-to-many (1:*)
many-to-many (*:*) - 102Multiplicity for Complex Relationships Number (or range) of ...possible occurrences of an entity type in an n-ary relationship when other (n-1) values are fixed.
- 103Multiplicity is made up of two types of restrictions on relationships:cardinality and participation.
- 104Cardinality Describes Participation Determines whether ..maximum number of possible relationship occurrences for an entity participating in a given relationship type.
all or only some entity occurrences participate in a relationship - 105Problems may arise when designing a conceptual data model called ... Often due to a misinterpretation of the meaning of certain relationships. Two main types of...... are called ...connection traps.
connection traps.
fan traps and chasm traps. - 106Fan Trap Where a model represents a relationship between entity types....but pathway between certain entity occurrences is ambiguous.
- 107Chasm Trap Where a model suggests the existence ofa relationship between entity types, but pathway does not exist between certain entity occurrences.
- 108Requirements Collection and Analysis Manage the requirements for a database system with multiple user views. Three main approaches:centralized approach;
view integration approach;
combination of both approaches. - 109Requirements Collection and Analysis Centralized approach Requirements for each user view aremerged into a single set of requirements.
A data model is created representing all user views during the database design stage - 110Requirements Collection and Analysis View integration approach Requirements foreach user view remain as separate lists.
Data models representing each user view are created and then merged later during the database design stage. - 111Data model representing single user view (or a subset of all user views) is called a Each model includes diagrams and documentation describing requirements for one or more but not all user views of database.local data model.
- 112Local data models are then merged at a later stage during database design to produce aglobal data model, which represents all user views for the database.
- 113Database Design Process of creating a design for a database thatwill support the enterprise’s mission statement and mission objectives for the required database system.
- 114Database Design Main approaches include:Top-down
Bottom-up
Inside-out
Mixed - 115Main purposes of data modeling include:to assist in understanding the meaning (semantics) of the data;
to facilitate communication about the information requirements. - 116A data model ensures we understand: -each user’s perspective of the data;
- nature of the data itself, independent of its physical representations;
- use of data across user views - 117Three phases of database design:Conceptual database design
Logical database design
Physical database design. - 118Conceptual Database Design Process of constructing a model of the data used in an ... Data model is built using the information in users’ requirements specification. Conceptual data model is source of ...enterprise, independent of all physical considerations.
information for logical design phase.
Entities, ER diagram, attributes, domains, keys - 119Logical Database Design Process of constructing a model of the data used in an enterprise based on a specific data model (e.g. relational), but independent of aparticular DBMS and other physical considerations.
- 120Conceptual data model is refined and mapped on to a ...logical data model.
Relations, normalization, user transaction support, constraints, future needs - 121Physical Database Design Process of producing a Describes ........ used to achieve efficient access to data. Also describes any associated ... Tailored to a ....description of the database implementation on secondary storage.
base relations, file organizations, and indexes
integrity constraints and secuirty measures.
specific DBMS system - 122Main steps to selecting a DBMS:define Terms of Reference of study;
shortlist two or three products;
evaluate products;
recommend selection and produce report. - 123Application Design Design of user interface and application programs that ◆ Database design and application design are ◆ Includes two important activities: –use and process the database.
parallel activities.
transaction design;
– user interface design - 124Application Design - Transactions ◆ An action, or series of actions.... Should define and document the...carried out by a single user or application program, which accesses or changes content of the database.
high-level characteristics of the transactions required - 125Application Design - Transactions ◆ Important characteristics of transactions: ◆ Three main types of transactions:– data to be used by the transaction;
functional characteristics of the transaction; –
output of the transaction;
– importance to the users;
– expected rate of usage.
retrieval, update, and mixed. - 126Prototyping ◆ Building a Purpose – to identify features of a system that... – to suggest – to clarify the – to evaluateworking model of a database system
work well, or are inadequate;
improvements or even new features
users’ requirements
feasibility of a particular system design. - 127Implementation ◆ Physical .... – Use DDL to create... – Use DDL to create – Use 3GL or 4GL to create the application programs. This will include therealization of the database and application designs.
database schemas and empty database files.
any specified user views.
database transactions implemented using the DML, possibly embedded in a host programming language. - 128Data Conversion and Loading ◆ Transferring any ◆ Only required when – DBMS normally has ◆ May be possible toexisting data into new database and converting any existing applications to run on new database.
new database system is replacing an old system.
utility that loads existing files into new database.
convert and use application programs from old system for use by new system. - 129Testing ◆ Process of running the database system with intent of finding errors. ◆ Use carefully planned test strategies and realistic data. ◆ Testing cannot show absence of ....... ◆ Demonstrates that database and application programs ....faults; it can show only that software faults are present.
appear to be working according to requirements. - 130Testing (Execution-Based) ◆ Should also test usability of system. ◆ Evaluation conducted against a usability specification. ◆ Examples of criteria include: –Learnability;
– Performance;
– Robustness;
– Recoverability;
– Adaptability. - 131Operational Maintenance ◆ Process of.........monitoring and maintaining database system following installation.
Monitoring performance of system.
– if performance falls, may require tuning or
reorganization of the database.
◆ Maintaining and upgrading database application (when required).
◆ Incorporating new requirements into database application. - 132CASE Tools ◆ Support provided by CASE tools include: -data dictionary to store information about
database system’s data;
- design tools to support data analysis;
- tools to permit development of corporate data model, and conceptual and logical data models;
- tools to enable prototyping of applications. - 133CASE Tools ◆ Provide following benefits:– Standards;
– Integration;
– Support for standard methods; – Consistency;
– Automation . - 134Data Administration and Database Administration ◆ The Data Administrator (DA) and Database Administrator (DBA) are responsible for ◆ DA is more concerned with.......of database system development lifecycle and DBA is more concerned with .....managing and controlling the corporate data and corporate database, respectively.
early stages
later stages. - 135Data Administration ◆ Management of data resource including: –database planning,
– development and maintenance of standards, policies and procedures, and conceptual and logical database design. - 136Database Administration ◆ Management of physical realization of a database system including: – physical database ... – setting ,........ controls – monitoring .........design and implementation
security and integrity
system performance, and
reorganizing the database. - 137Consider five types of integrity constraints: – RDERG.....required data
– domain constraints
– entity integrity
– referential integrity –
general constraints. - 138IEF - Entity Integrity ◆ Primary key of a table must contain ◆ ISO standard supports.... ◆ Can only have one ............. clause per table. Can still ensure....... for .........using ...a unique, non-null value for each row.
FOREIGN KEY clause in CREATE and ALTER TABLE statements:
PRIMARY KEY(staffNo)
PRIMARY KEY(clientNo, propertyNo)
PRIMARY KEY
uniqueness
alternate keys
UNIQUE:
UNIQUE(telNo) - 139IEF - Referential Integrity ◆ FK is column or set of columns that links each row in child table containing foreign FK to ........ ◆ Referential integrity means that, if FK contains a value.....row of parent table containing matching PK.
that value must refer to existing row in parent table.
◆ ISO standard supports definition of FKs with FOREIGN KEY clause in CREATE and ALTER TABLE:
FOREIGN KEY(branchNo) REFERENCES Branch - 140Data Definition ◆ Relations and other database objects exist in an....... ◆ Each environment contains one or more,...... and each ........consists of ...........environment
catalogs
catalog
set of schemas. - 141Schema is named collection of Objects in a schema can be......related database objects.
tables, views, domains, assertions, collations, translations, and character sets - 142View Dynamic result of one or more..... ◆ Virtual relation that does not.relational operations operating on base relations to produce another relation.
necessarily actually exist in the database but is produced upon request, at time of request - 143Views ◆ Contents of a view are defined as.... ◆ With view resolution, any operations on view are automatically translated into operations on relations from which it is derived. ◆ With view materialization, the view is stored as a temporary table, which is maintained as the underlying base tables are updated.a query on one or more base relations.
- 144Contents of a view are defined as a query on one or more base relations. ◆ With view resolution, any operations on view are automatically.... ◆ With view materialization, the view is....translated into operations on relations from which it is derived.
stored as a temporary table, which is maintained as the underlying base tables are updated. - 145CREATE VIEW ViewName [ (newColumnName [,...]) ] AS subselect [WITH [CASCADED | LOCAL] CHECK OPTION] ◆ Can ...... ◆ If list of column names is specified..... ◆If omitted, each columnassign a name to each column in view.
it must have same number of items as number of columns produced by subselect.
takes name of corresponding column in subselect. - 146Create view so that ...... CREATE VIEW Manager3Staff AS SELECT * FROM Staff WHERE branchNo = ‘B003’;manager at branch B003 can only see details for staff who work in his or her office.
- 147Create view of CREATE VIEW Staff3 AS SELECT staffNo, fName, lName, position, sex FROM Staff WHERE branchNo = ‘B003’;staff details at branch B003 excluding salaries.
- 148Create view of ...... CREATE VIEW StaffPropCnt (branchNo, staffNo, cnt) AS SELECT s.branchNo, s.staffNo, COUNT(*) FROM Staff s, PropertyForRent p WHERE s.staffNo = p.staffNo GROUP BY s.branchNo, s.staffNo;staff who manage properties for rent, including branch number they work at, staff number, and number of properties they manage.
- 149SQL - DROP VIEW DROP VIEW ViewName [RESTRICT | CASCADE] ◆ Causes definition of view to bedeleted from database.
◆ For example:
DROP VIEW Manager3Staff; - 150SQL - DROP VIEW ◆ With CASCADE, all related dependent objects ... ◆ With RESTRICT (default), if any other objects depend for their existence on continued existence of view being dropped,are deleted; i.e. any views defined on view being dropped.
command is rejected. - 151Objectives of SQL(T) ◆ Ideally, database language should allow user to: – create ...... – perform insertion,.... – perform simple and complex. ◆ Must perform these tasks with....... ◆ It must be.........the database and relation structures;
modification, deletion of data from relations
queries
minimal user effort and command structure/syntax must be easy to learn.
portable - 152SQL is a transform-oriented language with 2 major components:– A DDL for defining database structure.
– A DML for retrieving and updating data. - 153SQL is relatively easy to learn: –it is ............ - you specify....... information you require, rather than.......... to get it; – it is essentially free-format..non-procedural
what
how - 154An ISO standard now exists for SQL, making it both the........ and ............ standard language for relational databases.formal
de facto - 155Use extended form of BNF notation: - Upper-case letters represent...... - Lower-case letters represent user- - |indicates a choice.... - Curly braces indicate a .... - Square brackets indicate an...... - ... indicates optional......reserved words
defined words
among alternatives.
required element.
optional element repetition (0 or more). - 156.......... are constants used in SQL statements. ◆ All ............. must be enclosed in single quotes (e.g. ‘London’). ◆ All ..... not be enclosed in quotes (e.g. 650.00).Literals
non-numeric literals
numeric literals must - 157Relational algebra and relational calculus are formal languages associated with the relational model. ◆ Informally, relational algebra is a (high-level) ..........and relational calculus a ...... ◆ However, formally both are equivalent to one another . ◆ A language that produces a relation that can be derived using relational calculus is.....procedural language
non-procedural language.
relationally complete. - 158Relational algebra operations work on one or more relations to ......without ...... ◆ Both operands and results are relations, so output from one operation can become... ◆ Allows expressions to be.... This property is called closuredefine another relation
changing the original relations.
input to another operation.
nested, just as in arithmetic. - 159Relational Algebra ◆ Five basic operations in relational algebra: .... ◆ These perform most of the data retrieval operations needed. ◆ Also have ......... which can be expressed in terms of 5 basic operations.Selection, Projection, Cartesian product, Union, and Set Difference.
Join, Intersection, and Division operations, - 160Selection (or Restriction) ◆ spredicate (R) – Works on a single relation R and defines a relation that contains .......only those tuples (rows) of R that satisfy the specified condition (predicate).
- 161Projection ◆ Pcol1, . . . , coln(R) – Works on a single relation R and defines a relation that contains a ..........extracting the .....vertical subset of R,
values of specified attributes and eliminating duplicates. - 162Union ◆ RÈS – Union of two relations R and S defines a relation that contains all the ...... – R and S must be.... ◆ If R and S have I and J tuples, respectively, union is obtained by .....tuples of R, or S, or both R and S, duplicate tuples being eliminated.
union-compatible.
concatenating them into one relation with a maximum of (I + J) tuples. - 163Example - Union List all cities .....where there is either a branch office or a property for rent.
- 164Set Difference ◆ R–S – Defines a relation consisting of the tuples that are in relation R, but not in S. – R and S must be....union-compatible.
- 165List all cities wherethere is a branch office but no properties for rent.
- 166Defines a relation consisting of the set of ... – R and S must be .... ◆ Expressed using basic operations:all
tuples that are in both R and S.
union-compatible.
- 167List all cities where there is.....both a branch office and at least one property for rent.
- 168Defines a relation that is theconcatenation of every tuple of relation R with every tuple of relation S.
- 169List the names and .....comments of all clients who have viewed a property for rent.
- 170Cartesian product and Selection can be reduced to a single operation calleda Join.
- 171Join is a derivative of ... ◆ Equivalent to performing a Selection, using...... predicate as selection formula, over Cartesian product of the two operand relations.Cartesian product.
join - 172...... ◆ One of the most difficult operations to implement efficiently in an RDBMS and one reason why RDBMSs have intrinsic performance problems.join
- 173Various forms of join operation –Theta join
– Equijoin (a particular type of Theta join) – Natural join
– Outer join
– Semijoin - 174...............join (q-join) Defines a relation that contains tuples satisfying the..... F from the...... product of R and S.Theta
predicate
Cartesian
- 175Degree of a Theta join is the If predicate F contains only equality (=), the term ...........is used.sum of degrees of the operand relations R and S.
Equijoin - 176join An Equijoin of the two relations R and S over all ............ One occurrence of each ......... is..........the result.Natural.
common attributes x.
common attribute
eliminated from
- 177To display rows in the result that do not have matching values in the join column, useOuter join.
- 178........ is join in which tuples from R that do not have matching values in common columns of S are also included in result relation.Left outer join
- 179Semijoin Defines a relation that contains thetuples of R that
participate in the join of R with S.
- 180Defines a relation over the attributes C that consists of set of tuples from R that ........ tuple in S...match combination of every
example : Identify all clients who have viewed all properties with three rooms.
- 181How many◆properties cost more than £350 per month to rent?
- 182Example – Grouping Operation Find the number of..... staff working in each branch and the sum of their salaries
- 183Relational calculus query specifies ......... to rather than.......... it. – No description of how to evaluate a query. ◆ In first-order logic (or predicate calculus), predicate is a ...... ◆ When we substitute values for the arguments, function yields an expression, called a......, which can be either true or false...what is be retrieved
how to retrieve
truth-valued function with arguments.
proposition - 184If predicate contains a variable (e.g. ‘x is a member of staff’), there must be a range for x. ◆ When we substitute some values of this range for x, proposition may be true; for other values, it may be false. ◆ When applied to databases, relational calculus has forms:............tuple and domain.
- 185A........ is a table with columns and rows. – Only applies to...... structure of the database, not the...... structure.relation
logical
physical - 186.........is a named column of a relationAttribute
- 187Domain is the set of ............. for one or more......allowable values
attributes - 188Tuple is a..... ◆ Degree is the number of attributes in a relation.row of a relation.
- 189Cardinality isthe number of tuples in a relation.
- 190........... is a collection of normalized relations with distinct relation names.◆ Relational Database
- 191Any subset of Cartesian product isa relation
- 192Any subset of these ordered triples is arelation
- 193Relation schema Named relation defined...... and domain name pairs. ◆ Relational database schema – Set of relation schemas, each with aby a set of attribute
distinct name. - 194Relation name is distinct from all other relation names in relational schema. ◆ Each cell of relation contains exactly..... ◆ Each attribute has a ...... ◆ Values of an attribute are all from the.....one atomic (single) value.
distinct name
same domain. - 195Each tuple is......; there are ..... tuples. ◆ Order of attributes has ..... ◆ Order of tuples has .....distinct
no duplicate.
no significance.
no significance, theoretically - 196An attribute, or set of attributes, that uniquely identifies a tuple within a relation.Superkey
- 197.......Key – Superkey (K) such that no proper subset is a superkey within the relation. – In each tuple of R, values of K uniquely identify that tuple (uniqueness). – No proper subset of K has the uniqueness property (irreducibility). Candidate
- 198Name the four Relational KeysPrimary Key
Candidate key
Alternate Keys
Foreign key - 199Candidate key selected to identify tuples uniquely within relation.Primary Key
- 200Candidate keys that are not selected to be primary key.Alternate Keys
Information
- Description
- data science Flashcards on Database Mangement , created by amanda morrow on 03/05/2017.
- Created by
- amanda morrow · May 2017
- Updated
- May 7, 2017
- Views
- 2
- Rating