What is database?
A collection of data, typically describing the activities of one or more related organizations.
Ralational database stored in machine.
Data Models
A data model is a collection of tools or concepts for describing data, the meaning of data, data relationships and data constraints.
ER Model overview
Help you understanding the data, what data can be modeling. Find what data can be stored.
Customer is a set, every customer can be described as 4 periods, “name, id, street, city”.
Ralationalship means the connect between objects.
ER diagram is a language with graph dscription.
Record-based logical (Relational) model is a table. Proposed on 1976. then ER diagram comes out.
Data Abstraction
1.Hide certain details of how data is stored and maintained.
2.Physical level: how and where data are actually stored, low level data structures are specified at this level.
3.Conceptual level (logical level): describes what data should be stored in the database, and relationship and semantics of the data.
4.View level: Relevant partial view of the database to be particular type of users.
5.View model: a groups of data including gpa,gender etc. two types of people , teacher and student, teacher can view all data, but stduent can only check parts of data such as only gpa.
Binary relationship is the most difficult part in this lecture.
Every concept build a table and find it’s relationship.
Database design
1.Requirement analysis
2.Conceptual database design. Often come out using ER diagram.
3.logical database design. Transfer ER diagram to logical form.
4.Schema Refinement. Find it’s good or not.
5.Physical database design.building index etc. to make it faster.
6.application and security design.
ER model
R of ER model means that relationship is the diamond on the ER diagram. Relation is the table on the relational model.
Entity is a object, using rectangular to represent.
Entities and attributes
An entity is an object in the real world that is distinguishable from other objects. An entity is described using a set of attributes whose values are used to distinguish one entity from another of the same type.
An entity set is a collection of entities of the same type. (an rectangular to represent, all objects in the set can be described with same attributes) Eg. eployee e1, e2, e3, e4, all objects have attributes name, age,etc.
A set ,two objects can not be the same (attributes)!
Different types of afftributes
1.Simple attributes-a single value
2.Composite attributes-contains several components
3.Muti-valued-contains more than one value.
4.Derived-some attributes can not neccessraily be stored, it can be found by other attributes such as age can be infered by year.
key
A key has only one meaning.
It is a set of one or more attributes whose combined values are unique among all occurrences in a given entity set. Uniqueness.
What attributes composition can only find one specific element.
Age can not be key, gender can not be key, phone number(share condition), name may not be key, if application tell you every student have the only id, so id can be a key.
A minimal set of attributes that uniquely identifies an entity is called a candidate key.Quantity is less. And it’s the only. Composite Key is Two or more attributes are used to serve as a key E.g., Name or Address alone cannot uniquely identify an employee, but together they can.If there are many candidate keys, we should choose one candidate key as the primary key.
Summary key
1.Key: An attribute or combination of attributes that uniquely identify an entity. A key means of specifying uniqueness.
2.Super Key: A key that can be uniquely used to identify an entity, that may contain extra attributes that are not necessary to uniquely identify entities.
3.Candidate Key: A candidate key can be uniquely used to identify an entity without any extraneous data. It is a minimal super-key. Often abbreviate the Candidate Key to just Key.
4.Primary Key: It is one of the candidate keys.
5.Alternate Key: A candidate key that is not the primary key is called an alternate key.
6.Composite Key: Key made up of multiple attributes.
7.Surrogate Key: Also called Artificial Key. It is the Surrogate primary key acting as the substitute of the primary key. It is generated artificial internally.
