Conceptual Design Flashcards
What are the three levels of ANSI-SPARC Architecture?
The three levels of ANSI-SPARC architecture are the external level, the conceptual level, and the internal level
What is the external level of ANSI-SPARC Architecture?
The external level is how the user actually views the database. The data of the database that is relevant to the user is described here.
What is the conceptual level of ANSI-SPARC Architecture?
The conceptual level is the community view of the database and it describes what data is actually stored in the database; the entities, attributes and relationships.
This level contains the logical structure of the database without thinking about any storage considerations.
What is the internal level of ANSI-SPARC Architecture?
The internal level describes the physical representation of the database on the computer; how it is actually stored.
What is meant by independence in a database?
Logical data independence means that users/applications are immune to changes to the logical structure of a database.
Physical data independence means that users/applications are immune to changes to the physical representation of the data.
logical changes would be on conceptual level, physical would be internal
What is a data model
A data model is “an integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on the data in an organization.”
Quote by Connolly & Begg
What are the three design phases?
Conceptual design, logical design, and physical design.
What happens in the conceptual design phase?
A semantic model of the information used is built, independent of all physical considerations.
What happens in the logical design phase?
A database model based on a specific kind of data model is constructed, independent of any particular DBMS.
DBMS = Database Management System
What happens in the physical design phase?
The DBMS is implemented; how the data is stored on the disk.
DBMS = Database Management System
What are the main components of an entity-relationship model?
Entities/Entity Types, Relationships, Attributes and Constraints.
What is an entity?
A uniquely identifiable data object or item of interested.
Can be physical or abstract.
Belongs to an entity type. (A group of entities with the same properties)
(Jake and Sam can be two distinct entities belonging the same entity type (student)
What is a relationship?
A relationship is a relation between two ore more entity types. E.g; A branch employs staff.
Branch and staff are entity types, Employs is a relationship.
What is the degree of a relationship?
The number of entity types involved.
What are attributes?
Attributes are facts, aspects, properties or details about an entity or relationship.
Entity “staff” might have a phone number, DOB or address.
Relationship “employs” might have a start date.
What is a simple attribute?
Has a domain in which its values are taken.
May be single valued or multiple valued. (e.g, one or several phone numbers)
May be a key attribute, part of a group of attributes which uniquely identify entities.
What is a composite attributes
An attribute that consists of multiple components each of which can be simple or composite.
E.g, parts of an address.
What is a derived attribute?
A value that is derivable from the value of one or several other attributes. E.g, age can be derived from date of birth.
What is a key attribute?
Keys usually consist of one attribute; national insurance number, serial number, registration number. These uniquely identify a person or object.
They can also consist of several attributes working together.
What is a weak entity type?
An entity type that is existence dependent on another entity type (via a relationship)
They are only uniquely identifiable through the relationship with another strong entity type.
A client can have a preference for a particular house. Client is a strong entity type, but preference is weak as it is dependent on client to be uniquely identifiable.
What is a cardinality constraint?
A cardinality constraint describes the maximum number of possible relationship occurrences for an entity participating in a relationship.
A branch might employ many workers, but a worker can only be employed by one branch
- Relationship “Branch employs staff”:
- A branch employs many staff
- A member of staff is employed by one branch
- Cardinality is 1:M
What is a participation constraint?
A participation constraint determined whether all or some entities participate in a relationship.
All branches have workers, and all workers belong to a branch.
Not all workers manage a branch, but all branches must be managed by a worker
- Relationship “Branch employs staff”:
- A branch may employ staff.
- A member of staff must be employed by a branch.
- participation is partial for branch, total for staff.
What is a multiplicity constraint?
A multiplicity constraint describes participation and cardinality together by labelling the participation of an entity type in a relationship.
Uses exact upper and lower bounds.
“A branch is managed by exactly one staff”
“A member of staff can manage at most two branches”
Staff -(1,1)-> Manages -(0,2)-> Branch
If there’s no upper bound, you can use “*”
(E.g, (0,*))
What is a non-binary relationship?
A relationship involving three entitities.
E.g, between staff, branch, and client.
* Staff registers client at a branch
* At a branch a member of staff registers many clients
* A client at a branch is registered by one member of staff
What is a recursive relationship?
A recursive relationship is a relationship type where the same entity type participates more than once in different roles.
E.g, Staff supervises staff.
Staff as an entity type appears twice, but one staff is supervising the other staff.
What is meant by subclasses and superclasses?
(Aspect of Enhanced Entity Relationships)
Subclass entity types inherit attributes from superclass entity types. It avoids specifying the same attributes twice.
E.g, “Vehicle” might be the superclass of subclasses, truck, bus and car.
What is meant by participation constraint of a supertype?
Determines whether every member in the supertype must participate as a member of a subtype.
Mandatory - Every member of a supertype must also be a member of a subtype, or Optional - Opposite of mandatory
What is meant by disjoint constract of a supertype?
Disjoint constraint indicates whether it is possible for a member of a supertype to be a member of one or more than one subtype.
What is a fan trap?
When a model represents a relationship between entity types, but the pathway between certain entity types is ambigious.
E.g, What branch a member of staff might work for.
What is a chasm trap?
When a model suggests the existence of a relationship between entity types, but the pathway between those occurrences does not exist.
