MIS 3376

what are the deletion rules in database?

1) *restrict rule (R)*: does NOT allow parent entity to be deleted 2) *cascade rule (C)*: deleting a parent entity also deletes the child entities 3) *set null rule (N)*: deleting a parent entity results in the child entities being "set null" 4) *set default rule (D)*: deleting a parent entity results in the child entities being "set default" *place the letter symbol next to child entity*

what are the stages of data modeling?

1) conceptual modeling 2) logical model/design 3) physical design

what are the 3 limitations of file-processing systems?

1) lack of data integrity 2) lack of standards 3) lack of flexibility/maintainability

what are the 2 fundamental problems that lead to these limitations?

1) lack of integration 2) lack of program-data independence

what are gerunds?

a product of decomposing m:n relationships *do not have partial keys

what is an entity class?

a set of entity types that have shared properties

what is an entity instance?

an instance (occurrence) of an entity

what is a database model?

blueprint for designing databases

what is metadata?

data that describes data

what is cardinality constraint in an ER model?

maximum # of entity instance to which another entity instance can be associated through specific relationship *shows the max # of entities and associated with the specific relationship (ONE teacher teaches MULTIPLE courses, etc)

what are relationship degrees/types in an ER model?

the number of entity/entity types that have a relationship with each other

what are the different cardinality ratios in an ER model?

- 1 : 1 - 1 : n - m : n m, n = many

what is an entity-relationship model?

- a graphical model to conceptually model databases - describes entities and the relationships of the entities

what are the four actions of data management?

Create Read Update Delete

what is participation constraint in an ER model?

specifies whether an entity instance in a relationship needs to be connected to others

what are an attribute's role?

unique identifier: attribute whose values are distinct for each entity instance in the entity set (like ID, SSN, etc) *key*: attribute can be constituent part of a unique identifier *non-key*: any attribute that is not a constituent part of a unique identifier (like name, weight, classifications) //key attribute CAN be unique identifier BUT unique identifier CANNOT be key

what is a schema?

- description of metadata - map that shows how things are related

what are the characteristics of attributes?

- name - type - classification - category - source - domain - value - optionality - role

how to translate business rules into data model components?

- nouns: entities - verbs: relationships - attributes: nouns

what are business rules?

- ultimately determine how a database is modeled - formalized business practices related to data

what is data independence?

- when a schema at a lower level is changed, only the mapping information between this schema and higher-level schemas need to be changed - external views unaffected by changes to internal structure

what are an attribute's classifications?

*atomic*: can NOT be divided any further *composite*: can be meaningfully divided into smaller attributes

what are the arcs?

*exclusive arc*: one or the other, but not both (curve with arrows on both sides) *inclusive arc*: must be both (curve with endpoints) *either/both arc*: can be either or both (line)

what are an attribute's domain?

*implicit*: range of options (like age 1-100) *explicit*: set of values (like M or F) *optional*: can have no answer/null (like allergies)

what are symbols in entity-relationship model?

*primary way of modeling a relational database* - rectangle: entity - diamond: relationship - oval: attribute

what are the different degrees of relationships in an ER model?

*recursive/urinary*: n=1 *binary*: n=2 *ternary*: n=3 *quaternary*: n=4

what are an attribute's categories?

*single*: has one *multi-value*: has multiple (symbol; double open circle)

what are an attribute's source?

*stored*: things that don't change (like name) *derived*: things that change with time (like years of service)

what is the difference between strong and weak entities?

*strong*: - entity that always exists - has a unique identifier *weak*: - entity that only exists when based on a strong entity - does not have a unique identifier - symbol: double boxed or double diamond

what is total vs partial participation in an ER model?

*total/mandatory*: minimum = 1 (symbol is a line) *partial/optional*: minimum = 0 (symbol in a 0)

what happens when you delete from an instance?

- *CHILD entity type*: (in a m:n or 1:n relationship) requires NO action (like removing someone from a committee) - *PARENT entity type*: requires SOME action (like removing head from a committee)

what is the difference in internal, external, and conceptual schemas?

- *internal*: describes physical organization of the stored data - *external*: represents views for individual users and/or applications - *conceptual*: represents the global view of the structure of the entire database for a community of users

what is the difference between physical data independence and logical data independence?

- *physical*: capacity to change the internal schema without having to change conceptual or external schema - *logical*: external views unaffected by design changes (growth or restructuring) in conceptual schema

what are the components of a DBMS?

- data dictionary - query languages - data manipulation language - data definition language - reports

what is an entity type?

a collection of entities that have similar attributes *a conceptual way to describe an object

what is a database management system (DBMS)?

a collection of tools (software) that facilitate the processing of defining, constructing, and manipulating data in a database - SQL plays important role in DBMS

what is database system?

set of interrelated components working together for some purpose

what are attributes?

nouns that describe the entity (ex: name, type, classification, etc)

what are role names in an ER model?

optional, but can occasionally help with understanding (the verbs describing the action)

what is DBMS (database management system)?

provides a layer between the applications/users and the actual data ex: Oracle, MySQL, Microsoft SQL Server

what is database?

shared, integrated information technology structure that stores data and metadata