Работа с базами данных
<<  Урок по тысяча 2 класс петерсон Data Warehouse User Group June 23, 2011  >>
Database: An organized collection of “logically related” data that can
Database: An organized collection of “logically related” data that can
DBMS in a Web Server Environment: Very common when there are large
DBMS in a Web Server Environment: Very common when there are large
Business to Business E-Commerce Example using XML
Business to Business E-Commerce Example using XML
Data Warehouse “A database that stores and consolidates current and
Data Warehouse “A database that stores and consolidates current and
Illustration: Primary and Foreign Keys
Illustration: Primary and Foreign Keys
Entity, Referential Integrity
Entity, Referential Integrity
Cardinality: 1 to 1 (MS Access notation)
Cardinality: 1 to 1 (MS Access notation)
Cardinality: 1 to many (MS Access notation)
Cardinality: 1 to many (MS Access notation)
Example: Course Registration System Step 1. Draw Entities
Example: Course Registration System Step 1. Draw Entities
Example: Course Registration System Step 2. Add PK’s
Example: Course Registration System Step 2. Add PK’s
Example: Course Registration System
Example: Course Registration System
EXAMPLE: Package Delivery Tracking System
EXAMPLE: Package Delivery Tracking System
EXAMPLE: Package Delivery Tracking System
EXAMPLE: Package Delivery Tracking System
EXAMPLE: Package Delivery Tracking System
EXAMPLE: Package Delivery Tracking System
EXAMPLE: Package Delivery Tracking System
EXAMPLE: Package Delivery Tracking System
EXAMPLE: Package Delivery Tracking System
EXAMPLE: Package Delivery Tracking System
Example: Package Delivery Tracking System
Example: Package Delivery Tracking System
EXAMPLE: Airline Reservation System
EXAMPLE: Airline Reservation System
EXAMPLE: Airline Reservation System
EXAMPLE: Airline Reservation System
EXAMPLE: Airline Reservation System
EXAMPLE: Airline Reservation System
EXAMPLE: Airline Reservation System
EXAMPLE: Airline Reservation System
Example: Airline Reservation System
Example: Airline Reservation System
Query by Example (QBE)
Query by Example (QBE)
Insert (add) a complete record (values in all fields): INSERT INTO
Insert (add) a complete record (values in all fields): INSERT INTO
Insert (add) a complete record (values in all fields): INSERT INTO
Insert (add) a complete record (values in all fields): INSERT INTO
Data Extraction Queries: The Idea
Data Extraction Queries: The Idea
Database for Query Examples: Automated Garage Management System
Database for Query Examples: Automated Garage Management System
Normalization = The systematic process of “decomposing” a set of
Normalization = The systematic process of “decomposing” a set of
Normalization = The systematic process of “decomposing” a set of
Normalization = The systematic process of “decomposing” a set of
Normalization = The systematic process of “decomposing” a set of
Normalization = The systematic process of “decomposing” a set of
To what extent is a database normalized
To what extent is a database normalized
Q: What’s wrong with this table
Q: What’s wrong with this table
A: repeating values for a PK value
A: repeating values for a PK value
Q: What’s wrong with this table
Q: What’s wrong with this table
Q: What’s wrong with this table
Q: What’s wrong with this table
Картинки из презентации «Рисуем лето 3 класс» к уроку информатики на тему «Работа с базами данных»

Автор: T Slivinski. Чтобы познакомиться с картинкой полного размера, нажмите на её эскиз. Чтобы можно было использовать все картинки для урока информатики, скачайте бесплатно презентацию «Рисуем лето 3 класс.ppt» со всеми картинками в zip-архиве размером 15215 КБ.

Рисуем лето 3 класс

содержание презентации «Рисуем лето 3 класс.ppt»
Сл Текст Сл Текст
1Business Data Design Professor J. 76Joins: Involve more than one table Complex
Alberto Espinosa. Business Analysis and Nested Queries: Sub-queries (which compute
Data Design ITEC-630 Fall 2008. something) within queries. Complexity of
2Agenda. Introduction to database SELECT Queries.
concepts Data modeling & relational 77Simple SQL SELECT Queries. SELECT
database design Transitional artifacts: ProdID, ProdName, Type, Price (a list of
the CRUD matrix – linking requirements to fields) FROM Products (the table where the
data design Normalization Database data resides) WHERE Price>=300; (which
queries. rows to display) SELECT ProdName, Price
3Database Concepts. FROM Products WHERE Price>=120 AND
4Database: An organized collection of Type=“Percussion”; Note: the SQL DELETE
“logically related” data that can be command works identically to the SELECT
retrieved on demand Database Management command, but instead of displaying the
System (DBMS): Software that manages results, it deletes them. For example, the
databases (i.e., define, create, update, following DELETE command deletes all the
and query databases) Acts as intermediary records displayed with the previous query
between business applications and physical (it is not a bad idea to view the records
data files “Most powerful, scalable, before you delete them) DELETE ProdName,
flexible and effective business Price FROM Products WHERE Price>=120
applications rely on a well designed AND Type=“Percussion”; Note: an SQL query
database and a powerful underlying DBMS”. can not only contain a list of fields, but
Definitions. also any expression involving on or more
5The Old Way: Programs and Data files. fields. For example: SELECT Labor, Parts,
Data Files. API1. Data and program files Labor+Parts AS Charges (column name) FROM
were separate. You had to write individual Repairs WHERE Labor+Parts>=300;
programs to: define the data; upload it; 78Delimiters. When writing WHERE
update it; manipulate it; and or retrieve conditions and similar statements in SQL,
it. Examples: Accounting, Human Resources. one often needs to compare a field with a
Windows Unix, Linux. PC, Mainframe. particular value. The values need to be
HARDWARE. Application Programs. System written within delimiters that match the
Software. INSTRUCTION SET. data type. These are the delimiters: Text
6A Better Way: Using a DBMS. API2. – quotes: ex. WHERE UserID = “alberto”
Database. API1. A business application Date/time – pound sign ex. WHERE OrderDate
passes high level instructions to the > #01/07/2008# Number – nothing ex.
DBMS. The DBMS has capabilities to do all WHERE Amount > 200.
the necessary data management: data 79These queries yield a single number
definition, manipulation, and retrieval. result (i.e., a table with 1 column and 1
So, the business application does not have row) The only thing you can include in the
to worry about low level data management SELECT line are the fields you are
functions. Examples: Accounting, Human aggregating Aggregate functions you can
Resources, ERP, CRM. Oracle, Access, MS use: Avg, Sum, Min, Max, Count These
SQL Server. Windows Unix, Linux. PC, functions aggregate vertically a column of
Mainframe. HARDWARE. Database Application. (usually numeric) values (e.g., salaries,
DBMS. System Software. INSTRUCTION SET. payment amounts, etc.). SQL Queries With
7Advantages of Using Databases & Aggregate Functions. SELECT Count(*) as
DBMSs. Programs independent of data TotOrders FROM Orders WHERE OrderStatus =
structure Less data redundancy Better “Top Priority”. SELECT Avg(Price) AS
consistency in the data More flexibility AvgPrice FROM Products WHERE Price>=120
& scalability Easier to integrate AND Type=“Percussion”; SELECT Max(Price)
& share data Easier to develop AS MaxPrice, Avg(Price) AS AvgPrice FROM
business applications Easier to enforce Products WHERE Type=“Guitars”; Note: the
business rules/constraints Easier access AS clause is optional; it does not change
to data by users (e.g., queries, reports, the query results; it only changes the
forms, etc.). column label in the results. Note: you can
8Stand-alone DBMS. DBMS and database use more than one aggregate function in
work in the same computer: the user’s one SELECT command. Note: the Count
computer ? OK for personal productivity. function counts how many rows meet the
Stand-alone DBMS (e.g., MS Access). Where criteria, so it you can use any
Database. column you wish to count and you will get
9DBMS in a Client/Server Environment: the same results – the easiest thing is to
Better for corporate use ? the DBMS has use Count(*).
two components. DBMS Server: runs the 80The ONLY things you can include in the
“back-end” part of the DBMS and performs SELECT line are: (1) the fields you are
most of the data management functions – aggregating [e.g., Avg(Price)] (2) and the
e.g., queries, updates, etc. DBMS Client: fields you are using to group [e.g, Type]
runs “front-end” part of the DBMS that SELECT Type, Avg(Price) AS AvgPrice,
provides the user interface (e.g., data Max(Price) AS MaxPrice FROM Products WHERE
entry, screen displays or presentation, Price>=1000 GROUP BY Type SELECT Type,
report formatting, query building tools). Avg(Price) AS AvgPrice, Max(Price) as
DBMS Client. DBMS Server. Data Request MaxPrice FROM Products GROUP BY Type
(e.g., query). Response (e.g., query HAVING Avg(Price)>1000. SQL Queries
result). Retrieve, add, delete and/or With Aggregate Functions and Grouping.
update data. Database. Note: the WHERE clause is evaluated BEFORE
10DBMS in a Web Server Environment: Very the grouping. Note: the HAVING clause is
common when there are large numbers of evaluated AFTER the grouping.
users and would be impractical to deploy 81Table Join (2 ways): SELECT
and install a DBSM client ? access to the Orders.OrderNo, OrderStatus, ClientID,
database is done through a browser (e.g., LineItem, ProdID, Qty FROM Orders,
on-line purchases). Request (ex. get a LineItems WHERE Orders.OrderNo =
price quote, place an order). Response LineItems.OrderNo; Join Condition. Table
(ex. query results with HTML-formatted Product (WRONG!! Don’t forget the join
product price or order confirmation condition): SELECT Orders.OrderNo,
notice). ClientID, LineItem, ProdID, Qty FROM
11Business to Business E-Commerce Orders, LineItems; Complex SELECT Queries
Example using XML. e.g., supplier. with Joins. Tables: Orders (OrderNo,
Internet. e.g., buyer. DBMS (e.g., MS SQL ClientID, OrderDate, OrderStatus)
Server). DBMS (e.g., Oracle). INSERT LineItems (OrderNo, LineItem, ProdID,
query. XML Document (e.g., Purchase Qty).
Order). XML Processor. XML Processor. XML 82Complex SELECT Queries with Joins:
Document (e.g., Purchase Order). SELECT TIPS. COMPLEX queries that JOIN 2 tables
query. are identical to SIMPLE queries, except
12Data Warehouse “A database that stores for 2 additional rules you MUST ALWAYS
and consolidates current and historical apply: The two tables need to be JOINED
data from various systems (internal and through the common field that links them
external) with tools for management e.g., WHERE Orders.OrderNo =
reporting and sophisticated analysis—i.e., LineItems.OrderNo ANY time you refer to a
Datamining”. Business Intelligence. COMMOND FIELD that exists in both tables,
13Most Common Database Models. you must use a TABLE PREFIX to eliminate
Hierarchical (of historical interest only) the ambiguity e.g., SELECT Orders.OrderNo;
Network (of historical interest only) WHERE Orders.OrderNo = 990001 For complex
Relational Object Oriented (new). queries that JOIN 3 or more tables apply
14Relational Database. For a database to rule 1 for EACH link, and always apply
be truly relational, it must comply with rule 2 – e.g., SELECT Clients.ClientID,
12 rules defined by its inventor (Dr. E. ClientName, Orders.OrderNo, OrderStatus,
F. Codd). No commercially available LineItem, ProdID, Qty FROM Clients,
database complies with the full set of Orders, LineItems WHERE Clients.ClientID =
rules, but the 12 rules are used as Orders.ClientID AND Orders.OrderNo =
guidelines for sound database design. Rule LineItems.OrderNo.
1 states that data should be presented in 83Mapping QBE to SQL. SELECT. FROM. JOIN
tables Rule 2 states that data must be CONDITION. WHERE. ORDER BY.
accessible without ambiguity We will talk 84Nested Queries w/Aggregates. Display
more about other rules later (i.e., about above average quantities in line items –
entity integrity and referential integrity i.e., order number, line item, product ID
– stay tuned). and quantity for any line item in which
15Implications about Rule 1. A the quantity ordered is above the average
relational database must have: Tables: or quantity ordered in all orders: SELECT
“entities” Every table has a unique name OrderNo, LineItem, ProdID, Qty FROM
Ex. Students, Courses Fields: or LineItems WHERE Qty>(SELECT Avg(Qty)
“columns”, “attributes” Every field has a FROM LineItems) Tip: prepare the sub-query
unique name within the table Ex. Students first: SELECT Avg(Qty) FROM LineItems.
(StudentID, StudentName, Major, Address) 85Nested Queries w/Aggregates. Display
Ex. Courses (CourseNo, CouseName, product ID’s and total quantities ordered
CreditPoints, Description) Records: or for that product for totals exceeding 1000
“rows”, “tuples”, “instances” Every record units (2 solutions): SELECT ProdID, TotQty
is unique (has a unique field that FROM (SELECT ProdID, Sum(Qty) AS TotQty
identifies it) Ex. {“jdoe”, “John Doe”, FROM LineItems GROUP BY ProdID) WHERE
“CS”, 5000 Forbes Ave.) Ex. TotQty > 1000 Tip: again, prepare the
{“MGMT-352-001”, “MIS”, Fall 2002, “A sub-query first: SELECT ProdID, Sum(Qty)
great course”}. AS TotQty FROM LineItems GROUP BY ProdID
16Implications about Rule 2. Unambiguous Alternative solution without sub-query:
reference ? Table.Field WHERE Record SELECT Prod ID, Sum(Qty) AS TotQty FROM
Search Ex. Students.StudentID ? refers to LineItems GROUP BY ProdID HAVING Sum(Qty)
the StudentID field/column of the Students > 1000.
table Ex. Courses.CourseName WHERE 86Nested Queries w/Lists. Produce
CourseNo = “ITEC-630” ? more specifically, Sub-Query First – e.g., a single-column
refers to the value in the CourseName table (list) SELECT PartNumber FROM
field/column of the Courses table in the Shipments WHERE SupplierID = "S5“
record/row in which the field/column GROUP BY PartNumber HAVING
CourseNo is “ITEC-630”, that is “System Avg(Qty)>200; Then enclose the
Requirements”. Sub-Query in parenthesis and use with IN
17Object Oriented (OO) Databases. OO keyword SELECT DISTINCT PartName FROM
languages + added database functionality, Parts WHERE PartNumber IN (SELECT
or Database products + added OO PartNumber FROM Shipments WHERE SupplierID
programming facilities Similar to = "S5" GROUP BY PartNumber
relational databases “Classes” (a grouping HAVING AVG(Qty)>200); Another example:
of similar objects -- like tables) SELECT DISTINCT PartName FROM Parts WHERE
“Objects” (an instance of a class -- like PartNumber IN (352, 353, 354).
records) “Object properties” (object 87Database for Query Examples: Automated
attributes -- like fields) Plus: Methods Garage Management System.
(i.e., procedures or programs) Programs 88Example: Calculate the parking fee for
embedded in classes and objects Other OO one transaction at a garage and update the
Properties (inheritance, encapsulation, corresponding field. Tables:
etc.). Transactions(TransNo, LocID, EntryTime,
18Terminology Equivalence. ERD or Data ExitTime, FeeCharged [calc])
Model. OO Database. Relational Database. GarageLocations(LocID, HourlyRate)
Other Terms Used. Entity. Class. Table. Calculate rounded hours for transNo=2:
Instances. Objects. Records. Rows, Tuples. SELECT
Relationship. Relationship. Relationship. Round(24*(ExitTime-EntryTime)+0.5-5/60,0)
Attributes. Properties. Fields. Columns. FROM Transactions WHERE TransNo=2;
19Database Management Systems (DBMS). Calculate parking fee charged for
Software that manages databases i.e., TransNo=2: SELECT
define, create, update, and query Round(24*(ExitTime-EntryTime)+0.5-5/60,0)*
databases e.g., MS Access, MS SQL Server, ourlyRate FROM Transactions,
Oracle. GarageLocations WHERE
20DBMS Functions and Tools. Performs 3 Transactions.LocID=GarageLocations.LocID
main functions: Data definition (define, AND TransNo=2; Update parking fee charged
create databases) Data manipulation (data for TransNo=2: UPDATE Transactions,
entry, updates) Data retrieval GarageLocations SET FeeCharged =
(extraction, reports, displays) Plus Round(24*(ExitTime-EntryTime)+0.5-5/60,0)*
additional database tools: Data ourlyRate WHERE
dictionary: data about the database Visual Transactions.LocID=GarageLocations.LocID
tools: report & form design Data AND TransNo=2;
modeling & database design tools 89Database Design Issue #5: “Normalize”
Macros and programming languages Your Design.
Internet/web features, etc. Examples: 90Database Design Goals. Data integrity
Oracle, DB2, Visual FoxPro, MS Access (Entity and Referential Integrity – ERD’s)
& MS SQL. Avoid anomalies in the data No data
21Database Design. redundancy Record the data in one place
22Database Design Goals. Data integrity only Efficient data entry Duplicate data
Avoid anomalies in the data No data means having to enter the same data more
redundancy Record the data in one place than once Consistency Duplicate data can
only Efficient data entry Duplicate data lead to inconsistencies when the data
means having to enter the same data more changes e.g., 2 different addresses for
than once Consistency Duplicate data can same client Flexibility and easy evolution
lead to inconsistencies when the data East to maintain, update and add new
changes e.g., 2 different addresses for tables. Normalization.
same client Flexibility and easy evolution 91Why Normalization? Question: if a data
East to maintain, update and add new model/ERD is sound and all entity
tables. integrity, referential integrity,
23Database Design Issue #1: Enforce update/delete and business rules have been
Entity Integrity. well implemented, does this guarantee a
24Entity Integrity. Is ensuring that good database design? Answer: not
every record in each table in the database necessarily. If your design is not
can be addressed (i.e., found) – this “normalized”, you could have redundant
means that there each record has to have a data, and that would be a BAD thing
unique identifier that is not duplicate or (design). Normalization should yield the
null (i.e., not blank) Examples: every most efficient way to organize and record
student has an AU ID; every purchase order the data internally—not necessarily how
has a unique number; every customer has an users want to see the data, but what makes
ID Primary key (PK) ? helps enforce Entity more sense for non-redundant data storage
Integrity: Field(s) that uniquely We can later build user table views (i.e.,
identifies a record in a table (e.g., AU what the user wants or needs to see) by
user ID) Entity integrity = PK is not querying these normalized tables.
duplicate & not blank PK can be: A Redundancy: only PK and FK (e.g., client
single field (e.g., UserID), or Or more ID’s) values should appear in multiple
than one field (e.g., OrderNo, LineItem). tables (because they are needed to link
25Database Design Issue #2: Enforce tables) ? Non-key data (e.g., client last
Referential Integrity. name) that appears in multiple tables is
26Referential Integrity. Is ensuring “redundant”.
that the data that is entered in one table 92Example. You gather requirements from
is consistent with data in other tables users and one user gives you this table
Examples: purchase orders can only be and tell you that she would like the
placed by valid customers; accounting system to collect this data. How would you
transactions can only be posted to valid organize this data internally in the
company accounts Foreign key (FK) ? helps database?
enforce referential Integrity: A field in 93Normalization = The systematic process
a table that is a PK in another table That of “decomposing” a set of unorganized
is, a field that “must” exist in another tables with redundant data into smaller,
table This is how referential integrity is simpler, and more organized tables with
maintained. only minimal data redundant in key fields
27Illustration: Primary and Foreign and no data redundancy on non-key fields —
Keys. PK. FK. PK. i.e., from chaos to order. Normalization.
28Entity, Referential Integrity. PK. PK. Decomposition. Query. Decompose to most
FK. PK, FK. PK, FK. PK. Database Schema: efficient internal organization ? You can
The structure of the database, which always recover the original data format
contain tables, views, constraints, with a query ?
relations, etc. – just about everything, 94Normalization is a matter of degree --
except the data itself. the more normalized your design is, the
29Other Important Keys. Candidate Keys: lower the chances of having redundant data
Often there are more than one keys that Normal Forms (NF) (higher NF designs are
could serve as a primary key Example: more normalized): 1NF ? 2NF ? 3NF ? BCNF ?
Order, LineItem vs. Order, ProdID Example: PJNF ? DKNF ? 4NF ? 5NF The process of
AU ID, SSN, AU Login ID These are called normalizing a design to 3NF may seem
candidate Any candidate can be selected as complex, but the concept is very simple:
the primary key Alternative Keys: Once a (1) Minimize data redundancy in key
primary key has been selected from the attributes -- i.e., data in key fields can
choice of candidate keys, the other keys be entered in more than one table (2)
(not used as PKs) are referred to as Eliminate data redundancy in non-key
“alternative keys”. attributes -- i.e., data in non-key fields
30Database Design Issue #3: Develop a should be entered only in one table (3)
Data Model or Entity-Relationship Diagram Ensure that every piece of data (each
(ERD). non-key attribute) can be unambiguously
31Entities. Relationships. Data Model located by its PK (4) Each incremental NF
Example (Entity Relationship gets us a step closer in this direction.
Diagram--ERD): Course Registration System. Degree of Normalization.
Courses. Instructors. CourseNo. 95To what extent is a database
InstructorID. Teach. CourseDescription. normalized? Normalization is a matter of
LastName. Many. 1. FirstName. degree Measured in what is called “normal
InstructorID. CreditPoints. Telephone. forms” (NF) 1NF, 2NF, 3NF, etc., higher NF
EMailAddr. PreRequisites. ClassroomNo. 1. = more normalized 3NF Good enough for most
Students. Includes. StudentID. Many. applications BCNF ? Boyce-Codd NF (more
Enrollments. LastName. FirstName. Enrolls. robust version of 3NF) Mostly of academic
StudentID. SSN. CourseNo. Department. interest (and complex applications): 4NF,
Many. 1. College. Comments. Major. 5NF or PJNF (Project Join), DKNF
EMailAddr. (Domain-Key) ? More advanced
32Cardinality. Entities. Relationships. theoretically, little practical use ?
Data Model Example (Entity Relationship Useful for research and formal methods
Diagram--ERD): Course Registration System. only. Normal Forms.
1 to Many. Enrolls. Includes. Teaches. 96Q: What’s wrong with this table? A:
33The Textbook’s ERD Notation. Entities. Data in PayDate & Amount fields not
Instructors. Courses. Relationships. single-valued —i.e., they have repeating
Teach. InstructorID. CourseNo. LastName. values.
FirstName. InstructorID (FK). CourseDescr. 97A: repeating values for a PK value ?
Telephone. EMail. CreditPoints. PreReqs. PK is duplicate. Similar Table, Same
34Peter Chen’s ERD Notation. Problem.
Instructors. Course. PK. InstructorID. PK. 98First Normal Form (1NF). A “TABLE” is
CourseNo. Teaches. LastName. in 1NF if there are no multi-valued
CourseDescription. FirstName. FK1. attributes and no PK is duplicated i.e.,
InstructorID. Telephone. CreditPoints. attributes are “atomic” A “DATABASE” is in
EMail. PreRequisites. 1NF if ALL its tables are in 1NF.
35Entity-Relationship Diagrams (ERDs) 99Decomposition to 1NF: Create a
i.e., Conceptual Data Modeling. Similar to separate table where the repeating values
a class diagram, but without methods and can be recorded as rows.
generalizations Data-oriented modeling 100? Decomposition.
method that describes the data and 101Q: What’s wrong with this table? A:
relationships among data entities Goal: Some data in the Client and OrderDate
capture meaning of the data 2 main ERD or fields are entered twice i.e., some
data model constructs: Entities and its non-key data are redundant i.e., there are
attributes Relationships between entities. “partial dependencies” in the table (see
36Entity. “An object, person, place, next slide).
event or thing or which we want to record 102A. B. An attribute B is functionally
data” Equivalent to a table in a database dependent on attribute A if the value of a
Examples: instructors, students, valid instance of attribute A uniquely
classrooms, invoices, registration, determines the value of attribute B
machines, countries, states, etc. Entity Represented as: Functional Dependencies.
instance: a single occurrence of an entity 103StudentID StudentName StudentID
Example: Espinosa, Kogod 39, ITEC 630 StudentMajor. Functional Dependency
Entities can be identified in a Examples. What are the functional
requirements analysis description by dependencies in this relations? Clients
following the use of NOUNS. (ClientID, ClientName, City, State, Zip)
37Relationships. Relationships describe LineItems (OrderNo, LineItem, ClientID,
how two entities relate to each other ProdID, Qty).
Relationships in a database application 104Second Normal Form (2NF). Applies to
can be identified following the VERBS that tables with “composite” PKs (i.e., PK has
describe how entities are associated with more than one attribute) A “TABLE” is in
one another Examples: students enroll in 2NF if (1) it is in 1NF, and (2) non-key
courses countries have cities, etc. attributes are functionally dependent on
38Cardinality. Cardinality is an the whole PK, not on just part of it
important database concept to describe how (i.e., no partial dependencies) Note: we
two entities are related The Cardinality only need to worry about 2NF when PK
of a relationship describes how many contains more than one attribute (i.e.,
instances of one entity can be associated “composite”) That is: if a table is in 1NF
with another entity The cardinality of a and has a single PK, it is automatically
relationship between two entities has two in 2NF A “DATABASE” is in 2NF if ALL its
components: Maximum Cardinality: is the tables are in 2NF.
maximum number of instances that can be 105Decomposition to 2NF Move the partial
associated with the other entity – usually key (e.g., OrderNo) and the fields that
either 1 or many (the exact number is are functionally dependent on only that
rarely used) Minimum Cardinality: is the part of the key (e.g., ClientID,
minimum number of instances that can be OrderDate) to a separate table and make
associated with the other entity – usually that partial key the PK in that new table.
either 0 or 1 Symbols: 0 1 Many. 106? Decomposition.
39Cardinality (cont’d.). A relationship 107Q: What’s wrong with this table? A:
is fully described by describing the Some of the data in the ClientCity field
cardinality in both directions of the is redundant, because once we know who the
relationship: e.g., a client places zero ClientID is, we know the city where they
(i.e., optional) or many orders and each live i.e., there are “transitive
order must relate to only one (i.e., dependencies” in the table.
mandatory) client. Examples: 1 student can 108Transitive Dependencies. If a non-key
only park 1 (or 0) cars ? 1 to (0 or) 1 1 attribute C is functionally dependent on
client can place (0 or ) many orders ? 1 another non-key attribute B (B?C) and B is
to (0 or) many 1 student can enroll in (at in turn dependent on the PK attribute A
least 1 or) many courses and a course can (A?B) this implies C is transitively
have (0 or) many students ? (0 or) many to dependent on A (A?C) (through B or A?B?C),
(1 or) many. which will cause redundancies In 2NF, all
40Example: 2 Entities, 1 Relationship. non-key attributes are functionally
Zero or many. One and only one. Peter dependent on the PK Thus, in a 2NF table,
Chen’s notation & MS Visio software. a transitive dependency will occur every
Instructors. Course. PK. InstructorID. PK. time there is a functional dependency
CourseNo. Teaches. LastName. between any two non-key attributes.
CourseDescription. FirstName. FK1. 109OrderNo ClientID ClientName CourseNo
InstructorID. Telephone. CreditPoints. InstructorID InstructorName. Transitive
EMail. PreRequisites. Dependency Examples. Are there transitive
41ERD SYMBOLS (cont’d.) Note: high level dependencies in these relations? LineItems
conceptual models don’t show attributes, (OrderNo, LineItem, ProdID, Qty) LineItems
just entities. Employee. BioData. Has. 1 (OrderNo, LineItem, ProdID, ProdName,
to 1. MaximumCardinality (outer symbol). Qty).
Employee. FamilyData. Has. Mandatory. 110Third Normal Form (3NF). A “TABLE” is
Optional. Minimum Cardinality (inner in 3NF if (1) it is in 2NF and (2) non-key
symbol). Peter Chen’s notation using attributes depend on the PK and nothing
Systems Architect software. else That is, non-key attributes are NOT
42ERD SYMBOLS (cont’d.). ? Advises ? functionally dependent on other non-key
Have. Advisor. Student. 1 to Many. Maximum attributes (just on the PK) In other
Cardinality. 1 to Many (or None). Faculty. words, there are no transitive
Course. Teaches. Mandatory. Optional. dependencies A “DATABASE” is in 3NF if ALL
Minimum Cardinality. Peter Chen’s (“crow’s its tables are in 3NF.
feet”) notation using Systems Architect 111Decomposition to 3NF: Move the fields
software. with transitive dependencies to a separate
43Many to Many Relationships? Many to table.
Many. Orders. Products. Convert a 112? Decomposition.
Many-to-Many into 2 One-to-Many’s. Orders. 113In Summary. 1NF = no multi-value
Products. 1 to Many. LineItems. 1 to Many attributes (or no PK duplicates) 2NF = 1NF
(or None). Intersection Table. + the “whole” PK, not just part of it 3NF
44Cardinality: 1 to 1 (MS Access = 2NF + the PK and “nothing but” the PK
notation). Important! it is OK to have non-normalized
45Cardinality: 1 to many (MS Access designs, and some database applications
notation). may actually require a non-normalized
46Steps in data modeling Modeling. design, but you must have an understanding
Identify and diagram all ENTITIES Add PK of which normalization form you are
attributes – i.e., implement entity violating and a good reason for doing it.
integrity Ensure PK’s are non-null & 114Exercises. Text p.203, problem 3:
non-duplicates Identify and diagram all Indicate the normal form (PK underlined)
RELATIONSHIPS Note CARDINALITIES (1 to 1, and decompose to 3NF Class (CourseNo,
1 to n, n to n) Add FK attributes – i.e., SectionNo, RoomNo) Class (CourseNo,
implement referential integrity (this is SectionNo, RoomNo, Capacity) Class
automatic in some tools—MS Access) Add (CourseNo, SectionNo, CourseName, RoomNo,
remaining attributes. Capacity).
47ERD Example: Course Registration 115Exercises. POS System: Indicate the
System. Courses (CourseNo (PK), normal form (PK underlined) and decompose
CourseDescripition, InstructorID, to 3NF Sales (SaleNo, ClientID,
CreditPoints, ClassroomNo) PreRequisites ClientName, SaleDate, SaleAmount)
(CourseNo (PK), PreRequisiteNo (PK), SalesDetails (SaleNo, LineItem, SaleDate,
Comments) Students (StudentID (PK), ProdID, ProdName, Qty) Other Systems:
LastName, FirstName, SSN, Department, VideoRental (VideoNo, Date, MovieID,
College, Major, EMail) Enrollment MovieName, ClientID) VideoRental (VideoNo,
(StudentID (PK), CourseNo (PK), Comments) Date, ClientID, CheckoutDate, RentalDays)
Instructors (InstructorID (PK), LastName, Videos (VideoNo, MovieID, MovieName,
FirstName, Telephone, EMail) Classrooms MovieType) Videos (VideoNo, MovieID,
(ClassroomNo (PK), ClassroomName, VideoCondition) Movies (MovieID,
Building, BuildingRoomNo, Equipment, MovieName, MovieType, Producer,
Capacity) Note: PK denotes a primary key. ReleaseDate).
48Example: Course Registration System 116Exercise. Indicate the normal form and
Step 1. Draw Entities. decompose to 3NF.
49Example: Course Registration System 117FYI, Conceptually, normalization can
Step 2. Add PK’s (undeline/separate with a be thought of the opposite of a SELECT SQL
line). query. When you normalize, you decompose a
50Example: Course Registration System large table into simpler, smaller tables
Step 3. Add Relationships without redundancies. In contrast, when
(w/Cardinalities). PreRequisites. has. you query several small tables, the result
Course. Instructors. Teaches. PK,FK1. is a larger table in which redundancies
CourseNo. PK. CourseNo. PK. InstructorID. don’t matter. For example, the decomposed
PK. PreRequisiteNo. Includes. Assigned. tables of the exercise in the prior page
Enrollment. ClassRooms. Students. PK,FK1. can be reconstructed by querying the
StudentID. Enrolls. PK. ClassroomNo. normalized tables as follows: SELECT
PK,FK2. CourseNo. PK. StudentID. Companies.CompanyID, CompanyName,
51Example: Course Registration System Employees.EmployeeID, EmployeeName,
Step 4. Add FK’s. PreRequisites. Course. Departments.DeptID, DeptName FROM
has. Instructors. Teaches. PK,FK1. Departments, Companies, Employees WHERE
CourseNo. PK. CourseNo. PK. InstructorID. Companies.CompanyID = Employees.CompanyID
PK. PreRequisiteNo. FK1. InstructorID. AND Departments.DeptID = Employees.DeptID.
FK2. ClassroomNo. Assigned. Includes. 118Exercise. Indicate the normal form and
Enrollment. ClassRooms. Students. PK,FK1. decompose to 3NF (and then try to write an
StudentID. Enrolls. PK. ClassroomNo. SQL query to re-construct the original
PK,FK2. CourseNo. PK. StudentID. table).
52Example: Course Registration System 119FYI Only. Boyce-Codd Normal Form
Step 5. Add Remaining Attributes. Course. (BCNF): A more robust version of 3NF A
Instructors. PreRequisites. Has. PK. database is in BCNF when the database is
CourseNo. PK. InstructorID. PK,FK1. in 3NF when you substitute the PK with any
CourseNo. Teaches. PK. PreRequisiteNo. other Alternative Key That is, the
CourseDescription. LastName. FirstName. database is in 3NF for all Candidate Keys
FK1. InstructorID. Comments. CreditPoints. Domain-Key Normal Form (BKNF): All values
Telephone. EMail. FK2. ClassroomNo. entered in an attribute satisfy the
Assigned. Students. Includes. ClassRooms. constraints defined in the domain of that
PK. StudentID. PK. ClassroomNo. LastName. attribute An attribute’s domain is the
FirstName. Enrollment. ClassroomName. SSN. pool of data from which the attribute can
Enrolls. Building. PK,FK1. StudentID. draw its values Example: if we define a
Department. BuildingRoomNo. PK,FK2. constraint for the OrderID attribute
CourseNo. College. Equipment. Major. (e.g., 6 digits, from 000001 to 999999) in
Capacity. Comments. EMail. general (i.e., the domain), the OrderID
53Example: Course Registration System. attribute in every table that uses this
54EXAMPLE: Package Delivery Tracking attribute, must satisfy the same
System. constraints.
55Example: Package Delivery Tracking 120Transitional Artifact: The CRUD Matrix
System. ? Connecting Data Objects to Use Cases.
56EXAMPLE: Airline Reservation System. 121Data Objects. A data object is a
57Example: Airline Reservation System. person or thing you want to collect data
58Database Design Issue #4: Implement for: In a database application a data
Important Rules: Update, Delete and object is a table Examples: courses,
Business Rules. students, clients, invoices, orders,
59What can be updated/modified in the deliveries.
database and when? It is OK to update 122Identifying Data Objects. To identify
values in any non-PK fields, provided that data objects, refer to the Use Cases (or
referential integrity and business rules other requirements artifacts) and:
are respected It is OK to update values in Identify and highlight (or bold face) all
the PK in one table if it is not linked to nouns Inspect these nouns to see if they
a FK in another table, provided that represent possible system data objects But
entity integrity, referential integrity be careful, a noun may not refer to a data
and business rules are respected If a PK object, but simply to an attribute of a
is linked to a FK in another table, we data object A data object maps to a class
need to ensure that referential integrity (in a class diagram), entity (in a data
is maintained. Depending on what makes model) or table (in a database) A data
business sense, the update rule can be object has attributes (and behaviors if
either: U:R (Update:Restrict) – i.e., object is for a class) An attribute is
Disallow updates of values in the PK, or something you want to record about a data
U:C (Update:Cascade) – i.e., Allow object For example, in Students
updates, but cascade changes to all (StudentID, Name, SSN, Email)—Students
related FKs in other tables. Referential represents a data object and the data
Integrity: Update Rules. inside the parenthesis represents
60What can be deleted in the database attributes of that data object.
and when? It is OK to delete records in a 123The CRUD Matrix. A “transitional
table [only] if its PK is not linked to a artifact” is one that helps establish a
FK in another table If its PK is linked to relationship or cross reference between
a FK in another table, we need to ensure artifacts A CRUD matrix is a transitional
that referential integrity is maintained. artifact between Use Cases and Data
Depending on what makes business sense, Objects Helps ensure that the Use Cases
the delete rule can be either: D:R specified have all the necessary Data
(Delete:Restrict) – i.e., Disallow Objects to handle the data needs of the
deletion of records, or D:C application and, conversely, that the
(Delete:Cascade) – i.e., Allow deletion of collection of Data Objects identified
records, but cascade deletions in all cover the entire functionality specified
related tables that contain a FK linked to in the requirements. The Use Cases, if
this table. Referential Integrity: Delete properly specified, must describe all the
Rules. actions necessary to maintain all data
61What happens if (how is referential objects A CRUD matrix is a table that
integrity affected): We change an cross references which Use Cases:
instructor’s last name? We change an (C)reate, (R)ead, (U)pdate and/or (D)elete
InstructorID in the Course table? We data in these objects.
change an InstructorID in the Instructors 124Developing a CRUD Matrix. The CRUD
table? We delete a course? We delete an matrix has one row for every data object
instructor. Illustration of Update and identified and one column for every Use
Delete Rules. Instructors. Course. PK. Case specified (or the other way around)
InstructorID. PK. CourseNo. LastName. So, first create a column (or row) for
CourseDescription. FirstName. FK1. every Use Case in your model Every noun
InstructorID. Telephone. CreditPoints. highlighted in the Use Cases will suggest
EMail. PreRequisites. the need for data object to store the
62Most DBMS have features that allow you respective data you, so you need to create
to impose constraints in the data to meet a row (or column) for each of these data
rules imposed by a company when conducting objects. Then go through every cell in the
business: i.e., “business rules” – first Use Case and enter a C, R, U and/or
examples: CustomerAge >= 18 OrderQty D on the cell depending on whether the Use
>= 100 ProductPrice <= 1000 Case is creating, reading, updating or
PaymentDate <= PurchaseDate + 90. Other deleting records in the respective data
Data Integrity: Business Rules. object. The data objects should give you
63Database Design Issue #5: “Normalize” an indication of the entities (i.e.,
Your Design (we will discuss this later). database tables) that you will need in
64Database Queries. your Data Model (and database) And the
65Performs 3 main functions: Data C’s, R’s, U’s and D’s should give you an
definition (create databases) Data idea of the SQL queries that your
manipulation (enter & update data) application will need.
Data retrieval (data extraction) Plus 125Illustration. UC-102 reads data from
additional database tools: Data Table 1 ? It will require an SQL SELECT
dictionary: data about the database Visual query UC-101 creates a record in Table 1 ?
tools: report & form design Data It will require an SQL INSERT query UC-103
modeling & database design tools deletes records data from Table 3 ? It
Macros and programming languages will require an SQL DELETE query UC-102
Internet/web features, etc. Using Queries updates data in Table 2 ? It will require
(or proprietary features). DBMS Functions an SQL UPDATE query. UC-101. UC-102.
and Tools. UC-103. Table 1. C. R. Table 2. U. Table
66Queries. Often thought of as a method 3. D.
to retrieve data, but queries can also be 126CRUD Matrix Example for a Loan
used to define and manipulate data Processing Application. Use Case Data
Databases can be queried in many ways: Object. Submit a Loan Request. Evaluate a
Proprietary DBMS commands and languages, Loan Request. Book a Loan. Applicant. C.
which are unique to the particular DBMS Loan Application. C. R. Credit Score. C.
product, or Standard query R. Credit Report. C. R. Account History.
methods/languages (QBE, SQL, etc.), which C. R. Loan Request. C. R,U. R. Loan
most DBMS products support. Officer. R. Evaluation. C. R. Loan
67Query by Example (QBE) (Design View in Agreement. R. Loan Account. C. Loan Clerk.
MS Access) Visual interface using examples R. In a database application, these are
of data requested Similar to how you do tables and these are queries.
searches in the library. Structured Query 127ATM Application Example.
Language (SQL) Popular with power users 128ATM Use Case. Use Case ID. UC-100. Use
Works in most DBMS Can embed SQL commands Case. Withdraw Funds. Actors. (P)
in programs, web scripts, etc. Customer. Description. The customer
English-like commands, practical Exact, inserts card in the ATM, logs in with a
mathematical: relational algebra & pass code, and makes a selection from the
matrix math. Standard Query Methods. available choices to withdraw funds. Once
68Query by Example (QBE). Called Query in the funds withdrawal screen, the
“Design View” in MS Access Column labels customer is prompted to enter the amount
are the fields we want to retrieve In to withdraw. After the amount is entered,
table cells we enter “examples” of the the system will check for availability of
info we want. funds for that customer. Provided that
69Structured Query Language (SQL). funds are available, the system will
70SQL Commands Types Only 8 Commands!! dispense the amount requested in cash and
Data Definition: Create, Drop Data then debit that amount from the customer’s
Manipulation: Insert, Update, Delete, bank account. The system will record the
Union, Join Data Retrieval: Select. last withdrawal date in customer’s file
71One SQL Command (from CREATE to ;). and record transaction in ATM transaction
CREATE TABLE Employees (EmployeeID log . Priority. Non-Functional
integer, LastName char(24), FirstName Requirements. Assumptions. Source.
char(24), Birthday date, Phone char(10), 129ATM Use Case. Use Case ID. UC-101. Use
Notes memo); Fields created in Employees Case. Deposit Funds. Actors. (P) Customer.
table. ; = End of SQL Command. DROP TABLE Description. The customer inserts card in
Employees; SQL Commands: Data Definition the ATM, logs in with a pass code, and
Example: Create & Delete Table called makes a selection from the available
“Employees”. choices to deposit funds. Once in the
72INSERT: Add new records UPDATE: Modify funds deposit screen, the customer is
existing records DELETE: Delete records prompted to enter the amount to deposit.
UNION: Combine records from two tables After the amount is entered, deposit slot
JOIN: Combine columns from two tables. SQL door opens, customer places deposit
Commands: Data Manipulation. envelop in slot, deposit slot door closes.
73Insert (add) a complete record (values The system credits the customer’s account
in all fields): INSERT INTO Employees accordingly, records the last deposit date
VALUES (“ae”, “Espinosa”, “Alberto”, in the customer’s file and record the
12/12/2002, “885-1958”, “Looks tired, transaction in ATM transaction log.
needs a vacation”); Insert (add) partial Priority. Non-Functional Requirements.
record (values in some fields only): Assumptions. Source.
INSERT INTO Employees (EmployeeID, 130ATM Use Case. Use Case ID. UC-102. Use
LastName, FirstName) VALUES (“ae”, Case. Transfer Funds. Actors. (P)
“Espinosa”, “Alberto”); Update (modify) Customer. Description. The customer
record with new values: UPDATE Employees inserts card in the ATM, logs in with a
SET LastName=“Espinosa” WHERE EmployeeID = pass code, and makes a selection from the
“ae”; SQL Commands: Data Manipulation Add available choices to transfer funds. Once
& Update Records. in the funds transfer screen, the customer
74Data Extraction Queries: The Idea. How is prompted to enter the amount to
we store the data. How we display the transfer, from account and to account.
data. Organize database (design, create): After the information is entered, the
In the most efficient & consistent way checks for availability of funds. If funds
(internally) Not based on how you want the are available, it displays the transaction
data to look Produce the “virtual” and asks for confirmation. The customer
temporary tables the way you want them to confirms transaction and the customer’s
look using queries. account gets adjusted accordingly. The
75Data Extraction in SQL: The “SELECT” system records the last funds transfer
Command Note BOLDFACE denotes SQL date in the customer’s file and records
Keywords. SELECT field1, field2, etc. – the transaction in ATM transaction log.
columns to retrieve and display FROM Priority. Non-Functional Requirements.
table1, table2, etc. – tables that contain Assumptions. Source.
the data WHERE condition1 – which records 131ATM Use Case. Use Case ID. UC-103. Use
to retrieve AND [OR] condition2 ……. – Case. Balance Inquiry. Actors. (P)
further conditions GROUP BY field2, ….. – Customer. Description. The customer
to group results HAVING condition3 – like inserts card in the ATM, logs in with a
WHERE but after grouping ORDER BY field1, pass code, and makes a selection from the
field2, etc.... [DESC] [ASC] – to sort the available choice to inquire balances. The
query results SELECT can be followed by: machine prints balances, records the last
DISTINCT (SELECT DISTINCT eliminates balance inquiry date in the customer’s
duplicate rows from result) TOP n (lists file and records the transaction in ATM
only the top n rows of result – e.g. transaction log . Priority. Non-Functional
SELECT Top 5) * (lists all fields in the Requirements. Assumptions. Source.
table – e.g., SELECT * FROM …). 132ATM System’s CRUD Matrix. Use Case
76Simple Queries: Involve a single table Data Object. Withdraw Funds. Deposit
Queries with Aggregate Functions: When we Funds. Transfer Funds. Inquire Balances.
only want averages, totals, etc. Queries ATM. R,U. ATM Transaction Log. C. U. U. U.
with Aggregate Functions and Grouping: Customer File. R,U. R,U. R,U. R,U.
When we want averages, totals, etc. Customer Account. R,U. U. R,U. R. Customer
categorized by groups Complex Queries with Transactions. C. U. U.
Рисуем лето 3 класс.ppt
cсылка на страницу

Рисуем лето 3 класс

другие презентации на тему «Рисуем лето 3 класс»

«Data Mining» - Кибернетические методы. Мультидисциплинарность. Докладчики. Статистические методы. Александра Симонова, Мат-Мех, 5 курс. Недостатки. Метод "ближайшего соседа" или системы рассуждений на основе аналогичных случаев. Анализ связей (корреляционный и регрессионный анализ, факторный анализ, дисперсионный анализ).

«Windows Small Business» - Лицензия «на устройство» позволяет нескольким пользователям подключаться к серверу с одного устройства. Системные требования SBS 2003. «Мне нужен доступ к информации, когда я не в офисе». Интегрированные решения Продукты корпорации Майкрософт интегрированы для удобства пользователей. Плотники также ожидают ответа.

«Управление базами данными» - Технологии управления базами данными. Этапы проектирования. Настольные БД. Oracle 8. SQL Server 7.0. Vbase. Способы создания пустой таблицы. Informix. Sybase. Значения. Распределенные СУБД. Уникальная метка. Типы баз данных. Ввод данных. Мастера Microsoft Access. Гибридные БД. Отношения в БД. Объектно-реляционные СУБД.

«Большие объекты» - Операции с полями большого размера. Двухуровневое разбиение. Удалить N байт. Древовидное представление. Удаление. Упражнения. Алгоритм. Система ‘близнецов’ . Поля большого размера. Создание поля большого размера. Физическое представление объекта. Дескриптор поля большого размера. Современные СУБД. Рисунок.

«Data Mining» - Метод "ближайшего соседа" или системы рассуждений на основе аналогичных случаев. Кросс-проверка - известный метод получения оценок неизвестных параметров модели. Основная идея - разделение выборки данных на v "складок". Критерии: Точность распознавания Ошибка. Сокращение дерева или отсечение ветвей.

«Хранимые процедуры» - Создание, изменение и удаление хранимых процедур. Примеры использования. Сервер. Реализация триггеров. Типы триггеров. Триггеры. Триггер. Хранимые процедуры. Системные хранимые процедуры. Понятие хранимых процедур.

Работа с базами данных

11 презентаций о работе с базами данных


130 тем