Fichas

CPSC304 Midterm

Martin Palanca Inglés 49 fichas

Fichas del conjunto (49)

1metadatadata that describes the structure and the properties of the data
2databasea database is a collection of related data (usually stored on disk), typically describing the activities of one or more related organizations.
- a database provides some means to query this set of data as a unit, and usually some way to update it as well
- a database typically contains important data, is shared, is secured, is well-designed, and is of variable size
- typically models some real world enterprise (e.g. entities, relationships).
3database management system (DBMS)software designed to assist in maintaining and utilizing large collections of data. The alternative to using DBMS is to store the data in files and write application-specific code to manage it.
The DBMS is used to:
- define, load, query, and modify a database
- control access
- permit concurrent access
- maintain integrity
- provide utilities to manage, load, backup, recover, reorganize, etc. database objects
4why use a DBMS?- data independence and efficient access
- reduced application development time
- data integrity and security
- uniform data administration
- concurrent access, recovery from crashes
5File based approachBefore the existence of DBMS
- typical approach was to create ad-hoc or purpose-build data files
- specially built programs implemented various operations
- if new operations were desired, a new program was required to manipulate the data files
- if we want to change the data model, you’d need to update all the programs that access the data
- some things like transactions, security, and integrity constraints are difficult to implement with this approach
6relational databasein a relational DBMS, entities and relationships are implemented as tables with all kinds of constraints (rules) among them.
71. Conceptual modeling decisionsInvolves modelling the entities and relationships with ER (entity-relationship) diagrams
conceptual schema (logical schema)
describes the stored data in terms of the data model of the DBMS. In a relational DBMS, the conceptual schema describes all relations that are stored in the database.
- these relations contain information about entities and about relationships
- describes the set of relationships and entities within the conceptual model
82. Logical modelling decisionsWe will logically model using the relational model (e.g. tables)
- we’ll separate the logical and physical views of the data. The latter refers to the implementation of tables and indexes with appropriate storage structures and optimization techniques (e.g. clustering, partitioning)
9physical schemaspecifies additional storage details. Essentially, the physical schema summarizes how the relations described in the conceptual schema are actually stored on secondary storage devices such as disks and tapes.
10external schemaallows data access to be customized (and authorized) at the level of individual users or groups of users
- any given database has exactly one conceptual schema and one physical schema, but it may have several external schemas, each tailored to a particular group of users.
- each external schema consists of a collection of one or more views and relations from the conceptual schema
- a view is conceptually a relation, but the records in a view are not stored in the DBMS. rather, they are computed using a definition for the view, in terms of relations stored in the DBMS.
113. Deciding on queries- we will mainly use Structured Query Language (SQL) to extract data from our DBMS
- SQL is a declarative language - the query processor figures out how to answer the query efficiently.
124. Making our design more efficientWe’ll learn how to find a design that:
- captures all the information we need to store
- takes up less space
- it is easy to maintain
- we’ll look at different “Normal forms” to help us design a good, maintainable set of tables
13data independenceDBMS’s offer data independence. That is, application programs are insulated from changes in the way the data is structured and stored.
- users can be shielded from changes in the logical structure of the data, or change sin the choice of relations to be stored
- this property is called logical data independence.
- the conceptual schema insulates users from changes in physical storage details. This property is referred to as physical data independence.
14locking protocolA locking protocol is a set of rules to be followed by each transaction to ensure that, even though actions of several transactions might be occurring simultaneously, the net effect is identical to executing all transactions in some serial order
- e.g. shared lock when reading, exclusive lock when updating
entity-relationship model
15data modelA data model consists of:
- structure (e.g. entities and their properties)
- constraints (e.g. length, format)
- operations (e.g. CRUD, user_defined, compute_gpa)
16database design process: requirement analysisRequirement analysis:
- what data is going to be stored?
- how will data be stored?
- what are we going to do with the data
- who will get privileges to access the data?
17database design process: conceptual designConceptual design:
- a high-level description of the database
- appropriately specific that technical people can understand it
- but, not so specific that non-technical people can’t participate
18database design process: more designsMore designs:
- logical database design
- physical database design
- security design
19logical database designWe must choose a DBMS to implement our database design, and convert the conceptual database design into a database schema in the data model of the chosen DBMS
- We will consider only relational DBMSs, so the task in the logical design step is to convert an ER schema into a relational database schema.
20Entity-relationship modelallows us to describe the data involved in a real-world enterprise in terms of objects and their relationships and is widely used to develop an initial database
- involved in every step of the database design process
- allow the move from an informal description to a more detailed, precise description that can be implemented
21conceptual database designWhat are the entities and relationships in the enterprise?
- entities are usually nouns (e.g. students and courses)
- relationships are statements about 2 or more entities, often verbs
- what information about these entities and relationships should we store in the database?
- what constraints or other rules hold?
- in relational databases, this is generally encoded in an entity-relationship diagram
22entity vs entity set- an entity is a distinguishable object (e.g. movie, customer, student)
- an entity set is a collection of similar entities (e.g. all of the moves, all of the customers)
- an attribute is the property of (the entities of) an entity set
23entitiesEntities are not explicitly represented in ER diagrams. We represent entity sets.
- an entity is described using a set of attributes
- each attribute has a domain
- each entity set has a key, which is underlined in the ERD
24entity keyA key is a minimal set of one or more attributes whose value uniquely identities an entity in an entity set
- distinguishes an entity set
- there could be more than one candidate key; if so, we designate one of them as the primary key - the one with the fewest attributes, and this is known as the minimal key
25primary keyA primary key is the key chosen as the principle means to identify entities in an entity set
- only primary keys are whose in ER diagrams
- the E/R model forces us to designate a single primary key, though there may be multiple candidate keys
26superkeyis a key plus 0 or more attributes in the entity set. Super key stands for superset of a key. A super key is a set of one or more attributes that are taken collectively and can identify all other attributes uniquely.
27relationship- Let A, B be sets. A x B (the cross-product, combination) is the set of all pairs. A relationship is a subset of AxB
- a relationship is an association between two or more entities
- relationships can also have descriptive attributes. These attributes are used to record information about the relationship, rather than about any one of the participating entities. A relationship must be uniquely defined by the participating entities, without reference to the descriptive attributes.
- relationships cannot have keys
28relationship setA collection of similar relationships.
- e.g. Collection of all MoviePeople that have worked in Movies
29binary vs ternary relationshipsThe degree of a relationship is the number of participating entity types (binary, ternary, etc)
30self-referencing relationshipsThe same entity set could participate in different relationship sets or in different roles in the same set
31cardinalitiesA cardinality ratio for a relationship set specifies the number of relationships in the set that an entity can participate in. Let R be a relationship between sets A and B. R can have 1 of 4 cardinalities:
1. one-to-one from A to B
2. one-to-many from A to B
3. many-to-one from A to B
4. many-to-many from A to B
32key constraints- The ER notation can be read: “if you know the entity at the tail of the arrow, then you know the relationship (and the other entities involved)
- pointing towards the entity that there is only one of
33primary keys of relationshipsLet R be a relationship set between sets A and B. R’s primary key is:
34participation constraintsParticipation indicates if all entities must participate in the relationship
- an entity’s participation can be total or partial
- requiring total participation is a participation constraint and it is shown with a thick line
- important on deletions, if total participation is required, both entities must be deleted if one is deleted
35weak entitiesWe have thus far assumed that all entities have a key. This assumption does not always hold.
- Dependents is a weak entity set. A weak entity can be identified uniquely only by considering some of its attributes in conjunction with the primary key of another entity, which is called the identifying owner.
The following restrictions must hold:
- the owner entity set and the weak entity set must participate in a one-to-many relationship set or a one-to-one relationship set (one owner, many/one weak entities)
- we wouldn’t be able to uniquely identify this dependent with a many-to-many relationship
- the weak entity must have total participation
- a weak entity’s partial key can be indicated with a dashed underline
- weak entity sets and their identifying relationship sets are shown with thick lines
36class hierarchies (ISA relationships)Several entity sets can be abstracted by a more general entity set
- all attributes are inherited by sub-entities
- sometimes, an entity set contains some entities that do share many, but not all properties with the entity set. In this case, we want to define class (entity set) hierarchies.
- we say that the subclass specializes the superclass, or the superclass generalizes the subclass
37class hierarchies (ISA) - constraints1. overlap constraints
- determines whether subclasses are allowed to contain the same entity (e.g. can someone be an actor and a musician?
- if overlap is allowed, should be noted
2. covering constraints
- determines whether the subclasses listed cover all entities of the superclass
- if covering is true, should be noted
38ISA Hierarchies - why use them?Reasons for using ISA relationships and subclasses:
- do not have to redefine all the attributes
- can add descriptive attributes specific to a subclass
- to identify entity sets that participate in a relationship set as precisely as possible
39AggregationAggregation allows us to indicate that a relationship set participates in another relation.
- having a relationship between relationships is forbidden
- aggregation allows us to treat a relationship set as an entity set for the purposes of participation in (other) relationships
- the key for the aggregated relationship is the key of the relationship within the aggregation
40Aggregation vs ternary relationshipsWhen to use ternary relationships over aggregation:
- when there are no key constraints
- the relationship’s descriptive attribute applies to each pair of entity sets in the relationship
- aggregation can be used instead of a ternary relationship if need to impose additional constraints on the relationship
41functional dependenciessome attributes determine other attributes (e.g. postal code can determine province and city for an address entity)
- however, we cannot show this property on an ERD
42Entity vs attributeShould an address be an attribute of MoviePeople or an entity (connected to MoviePeople by a relationship?
Depends upon:
- the use we want to make of address information
- the semantics of the data:
* if we have several addresses per person, address must be an entity
* if a person has only one street address, then it should be an attribute
43Entity vs relationship attribute1) WorkOn2 does not allow a person to have more than one role in the same movie
- we want to associate the same pair (MoviePerson, Movie) with more than one set of values for the descriptive attributes?
2) Solution: change descriptive attribute into entities
44Entity vs relationship- The first ERD looks Ok as the director gets a separate assistant for each movie
- What if the director gets the same assistant for all movies? This causes redundancy and is misleading since it suggests each assistant is associated with the MoviePeople-Movie combination
Solution: make assistant an attribute of director
45Binary vs Ternary relationshipsEach policy is owned by just one person. Key constraint on policies would mean policy covers ONE dependent.
Additional constraints in 2nd diagram:
- for each dependent, exactly one policy of which which the dependent is the beneficiary
- for each policy, there is exactly one person who purchases it.
- here, two binary relationships are favoured
46Binary vs Ternary RelationshipsA ternary relation is favoured here.
- Drinks relates entity sets Person, Bar, Drink, and has descriptive attribute date.
- Can we use two binary relationships instead? No combination of binary relationships is an adequate substitute:
- P “likes” D, P “visits” B, and B “provides” D does not imply that P drinks D in B.
- aggregation can be used instead of a ternary relation if need to impose additional constraints (e.g. a person cannot have more than one drink in the same bar).
47Summary of conceptual designConceptual design follows requirement analysis, and yields a high-level description of data to be stored
- ER model is popular for conceptual design
- Basic constructs: entities, relationships, attributes
- Some additional constructs: weak entities, ISA relationships, and aggregation
- Note: There are many variations on ER model
- Constraints can be expressed in the ER model
- ER design is subjective. There are often many ways to model a given scenario. Analyzing alternatives can be tricky, especially for large enterprises.
- ensuring good database design: resulting relational schema should be analyzed and refined further.
48what do we want out of our logical schema representation?* ability to store data - w/o worrying about blocks on disk
* ability to query data easily
* a representation that is easy to understand
* a representation that we can easily adapt from conceptual schema
* separate from application programming language
49how did we get the relational model?* prior to the relational model, there were two main contenders from DBMSs:
*
network database systems
*
hierarchical database systems
*
Network databases had a complex data model
*
lots of pointers to follow around
*
not very flexible
*
hierarchical databases integrated the application in the data model
*
easier to understand than network databases (intuitive for some applications, but still fairly complex)