exam 70-461 querying microsoft sql server 2012 1/databaser 1.pdf · cardinality 1:1 --- one-to- one...
TRANSCRIPT
![Page 1: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/1.jpg)
Exam 70-461 Querying Microsoft SQL Server 2012
Database Development
![Page 2: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/2.jpg)
Course ObjectivesNormalizationER DiagramDBMSSQL CommandsConstraintsData integrity rulesSubquiriesIndexesFunctionsTriggersStored Procedures
Database Development
![Page 3: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/3.jpg)
Material:
Book:
Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012
Days Reading:
Database Development
![Page 4: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/4.jpg)
Tools:MS VISIO 2013
MS SQL SERVER 2012 or 2014 Evaluation Edition
Database Development
![Page 5: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/5.jpg)
Evaluation:Oral or MCQ Test
Database Development
![Page 6: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/6.jpg)
Outline:
Database Introduction
Data Modeling
Entity Relationship modeling
Normalization
Database Development
![Page 7: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/7.jpg)
General Terms
Data
Database
Database Management System
Database Development
![Page 8: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/8.jpg)
What is the ultimate purpose of a database management system?
Data Information Knowledge Action
Is to transform
![Page 9: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/9.jpg)
Data Modeling
The three level of data modeling,
1. Conceptual data model
2. Logical data model
3. Physical data model
![Page 10: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/10.jpg)
Abstraction Layers
• Conceptual– What data is held
• An Image and its meta-data• Entity-Relationship model (ERM)
• Logical– How data is organised in storage
• Block and Directory structure• Tables, keys
• Physical– How data is stored in bits
• JPEG as a stream of bytes• A Database as files and records stored in a DBMS-specific format
Abstraction
Realisation
(Refinement
Reification)
(Reverse Engineering)
(Engineering, Model-Driven development
![Page 11: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/11.jpg)
Conceptual data model
![Page 12: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/12.jpg)
Logical data model
![Page 13: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/13.jpg)
Logical data model
![Page 14: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/14.jpg)
Physical data model
![Page 15: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/15.jpg)
Physical data model
![Page 16: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/16.jpg)
Comparison
![Page 17: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/17.jpg)
Conceptual Design
![Page 18: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/18.jpg)
Logical Design
![Page 19: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/19.jpg)
Physical Design
![Page 20: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/20.jpg)
Comparison
![Page 21: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/21.jpg)
E R Diagram
Entity relationship Model
Specialized graphics
Inter-relationship between Entities
Use symbols
![Page 22: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/22.jpg)
E R Diagram (Chen Notation)
Three types of information
Boxes = Entity
Diamonds = Relationship
Oval = Attributes
![Page 23: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/23.jpg)
E R Diagram (Chen Notation)
Example:
Information about residents
Persons
City
Names
Population
Lives in
![Page 24: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/24.jpg)
E R Diagram
![Page 25: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/25.jpg)
Cardinality and Existence
![Page 26: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/26.jpg)
Cardinality
1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y. One entity of type Y can be associated with, at most, one entity of type X.
An example: the relationship between car and steering wheel. A car has only one steering wheel and a steering wheel can only be installed in one car.
1:M --- one-to-many --- One entity of type X can be associated with many entities of type Y. One entity of type Y can be associated with, at most, one entity of type X.
An example: the relationship between building and rooms. A building can have many rooms but a room can be in, at most, one building.
M:M --- many-to-many --- One entity of type X can be associated with many entities of type Y. One entity of type Y can be associated with many entities of type X. An example: the
relationship between a car and its options (such as air conditioning, ABS brakes). A car can have many options and an option can be installed on many cars.
![Page 27: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/27.jpg)
E R Diagram (Crow Foot)
![Page 28: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/28.jpg)
E R Diagram (Crow Foot)
![Page 29: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/29.jpg)
E R Diagram (Crow Foot)
![Page 30: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/30.jpg)
E R Diagram (Crow Foot Example)
![Page 31: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/31.jpg)
TASK 1:
Install MS VISO 2013
Construct following ER Diagram by using chen database notations. Watch video Create an ER Diagram.
![Page 32: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/32.jpg)
TASK 2:
Watch video Data Modeling in Visio 2013.
Construct following ER Diagram by using crow foot database notations.
![Page 33: Exam 70-461 Querying Microsoft SQL Server 2012 1/Databaser 1.pdf · Cardinality 1:1 --- one-to- one --- One entity of type X can be associated with, at most, one entity of type Y.One](https://reader030.vdocuments.us/reader030/viewer/2022041005/5ea92209369d8a0b24241d53/html5/thumbnails/33.jpg)
TASK 3:
Construct following ER Diagram by using chen database notations. Recreate the diagram into crow foot notation.