Software that provides controlled access to database records
When the same data exists in multiple locations, wasting storage
When a programming instruction defining a data's value is based on the value of another piece of data
The accuracy and consistency of data over its lifecycle (i.e passes ACID test)
Database that has multiple different tables with foreign keys in each table
A schema that shows table relationships between entities
Type of object (abstract or otherwise) that a DB stores data on. Instances are realizations of some entity
Property relating to an entity that may differ between instances
Derived attribute; attribute that can be deduced or derived from existing attributes of an entity
Multivalued attribute; attribute that may take multiple amount data entries
Mapping between instances
In a DB, this is shown through the PK-FK paradigm
Value with potential to represent information
Pure datatypes such as strings, integers, dates, currency etc.
Data that provides information on other data
Data that is assigned meaning and context
Attribute that uniquely identifies an instance of an entity
Key that defines its instance. These are used to give instances their own IDs
Key representing an instance with which there is a relation (possibly of an instance of another entity). These specify relationships between instances
Information on whether instances must have at least one relationship. An entity A has a mandatory relationship with B if all instances of A must have at least 1 relationship to an instance of B
Quantity regarding how many entities are included within the relationship
Unary relationship, relationship with two instances of the same entity, known as recursive relationship
Binary relationship, relationship between instances of two entities
Ternary relationship, relationship between instances of three entities, the polygamy of databases
Programming language that forms data push and pull requests to a database
Statement that defines or confines aspects of a business
Entity used to represent mappings of a M:M relationship
Entity with a composite primary key made of a selection of its attributes and its strong entity's PK
Entity that some weak entity's composite key depends on
Entitiy type that inherits all attributes of some other entity (called a supertype) along with having its own attributes. The supertype's primary key becomes a foreing key in the subtype
Making specialized subtypes from a common supertype
Making a generalized supertype from similar subtypes
Whether all possible subtypes are listed in the database or not
Complies with completeness constraint, represented with double lines
Doesn't comply with completeness constraint
Whether or not an instance of a supertype can be an instance of multiple subtypes
instance of a supertype can be an instance of a single subtype, this is represented by a 'd'
instance of a supertype can be an instance of multiple subtypes, this is represented by an 'o'
Data model based on an ERD
A table in an ERD representing an entity which has rows (instances) and columns (attributes) that represent attribute names and the attrributes of various instances. Relations:
- Must have only atomic attributes (so no multi valued attributes or composite attributes)
- Mustn't have a specific order for rows and columns
- Table name must be unique
- All rows must be unique (no two instances can be the same)
Verification process to ensure data integrity (i.e, data being entered is compatible with the system) constraints include:
- Domain constraints; Ensures data being entered passes a certain check that verifies that it fits the attribute's representation (i.e, appointment can't be booked in a date that has already passed, or that age cannot be greater than 200)
- Entity constraints; Primary keys can't be null values
- Referential constraints; Ensures foreign keys exist as primary keys in the related table
- Restrict; Prevent deletion of primary instance if there are foreign instances that index that primary instance's primary key
- Cascade; automatically delete all foreign instances that reference the primary instance to be deleted
- Set-to-Null; Set the foreign key in the foreign instance to null, this can't be done with weak or associative entities or entities with mandatory cardinality
- Simple; Attributes that take a value
- Composite; Attributes that are made up of other attributes (i.e, Address contains Street, Suburb, State), in a relational database, these are represented as their own attributes
- Multi valued; Attrivutres with multiple values, these are represented as their own relations in a relational database
- 1:1; Mapped as is
- 1:M; Foreign key on the many side
- M:1; Foreign key on the many side
- M:M; Connected to associative entity
- 1:1; Mapped as is
- 1:M; Foreign key on the many side
- M:1; Foreign key on the many side
- M:M; Connected to associative entity
- 1:1; Mapped as is
- 1:M; Foreign key on the many side
- M:1; Foreign key on the many side
- M:M; Connected to associative entity
Functional Dependency; where the value one attribute determines the value of other attributes, for instance A→BC means A determines B and C
Set of attributes that are functionally determined by some other attribute in a relation. To find the attribute closure of a record:
When an attribute is dependant on only a part of a composite primary key
When an attribute C is dependant on attribute B and attribute B is dependant on attribute A, so that means attribute C is dependant on attribute A
Reduduancy errors that can occur in a database:
ERD with the following 3 properties