COMP2004 - DB Conceptual modelling Flashcards
Entity Relationship Diagram
Specify entities in a system along with their relationships
Conceptual modelling
- model objects
- identify;
Relationships btwn them
Their cardinality and participation
Identify strong and weak entities
Modelling
- objects - from nouns in UML
- attributes - properties of objects
- associations - relationships btwn objects
- cardinality - 1 to many, many to many
- participation - optional, mandatory
Weak entities
Can only exist in the presence of a strong entity ( composite notation)
so on UPDATE/DELETE CASCADE
Composite notation
It is only called composite if the objects it refers to are really its parts, i.e. have no independent existence. For details, see the aggregation section below.
High level model - Conceptual model
Understanding at a high level the different concepts in the system
Middle level model - Logical model
Adding details to the data - middle step between the two
Low level model - physical model
how it is implemented in the database
Functional dependency
Constraint btwn two sets of attributes in a relation from a database
Relation
Is a set of tuples where each element is a member of a data domain. Each distinct domain used in the definition of a relation is called an attribute
Relation
Attribute
| | | | |
—————————–
Tuple | | | | |
—————————–
| | | | |
—————————–
The whole thing is relation
Deriving tables
Objects become tables with the primary key chosen appropriately
Tuple/ Row
A sequence or ordered list of finite length
Column/Attribute
Defines properties of an individual
Relation
Table
Tuple
Row
Attribute
Column
Domain/Type
Attribute type
Defines how a given item is constrained
Schema
Defines a structure of a database
Name ({ as })
Degree of relationship is the number of attributes in a table
Known as relation intension
Model
Structure
Operations
Constraints
Eg relational, semistructure (xml)
Relational model
Model
Schema
State
Super keys
Set of attributes which uniquely identify all other attributes
Values for the attributes in a superkey will identify one unique record
Candidate key
A subset of the super keys which contain the minimal number of attributes to uniquely identify a record
Primary key
One of the candidate keys which the user has specified will be the unique identifier for a record. Irreducible. Not null
PRIMARY KEY FIELDS ARE IN CAPITAL LETTERS
Foreign key
Refers to a candidate key in another table
Functional dependency
Where a subset of attributes A2 is determined by another subset of attributes A1 such that A2 depends on A1.
A1(determinant) ->A2 (dependent)
Means A1 determines A2 or A2 is determined by A1
ACID
Atomicity
Consistency
Isolation
Durability
Atomicity
All the operations in the transaction occur or nothing occurs
An operation must start and then run to dinish without being interrupted or stopped along the way. ATOMOS=indivisible
Consistency
Any transaction will bring the state of the database from one valid state to another (neither state can invalidate any of the defined rules).
Eg. Transaction transferring funds ensures that sum of both accounts is the same before and after.
Isolation
Events within a transaction are hidden from other transaction running concurrently. Uses synchronisation techniques.
Concurrent operations on the database must leave the system in a state as if they were run sequentially.
Durability
Once a transaction has been completed and committed it must guarantee these results survive subsequent malfunctions. The user must have a guarantee that what has happened has actually happened.
A change committed to the database must be permanent, regardless of other operations, power failure, crashes etc
Structure of a transaction
START A TRANSACTION
SQL STATEMENTS
COMMIT = the statements are installed permanently or ROLLBACK - the transaction aborts, database is restored to the original state
Two phase locking
Is the ensuring that within each transaction all lock actions precede all unlock actions. This ensures that any legal schedule of transactions remains conflict serialisible.
Semi structured data
Form of structured data that does not have a matching schema. It still has correct tags (is still well fromed) but tags might not be consistent across whole document.
Well formed XML
Is syntactically correct XML
Conforms to XML spec and can be parsed
Valid XML
Is gramatically correct for a given grammar.
Valid XML must also be Well Formed XML
DTD
Document Type Definition
