chapter 16 (p481 – 485, 496-501) fluency with information technology 4 th edition by lawrence...
TRANSCRIPT
![Page 1: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/1.jpg)
Chapter 16 (p481 – 485, 496-501)
Fluency with Information Technology4th edition
by Lawrence Snyder(slides by Deborah Woodall : [email protected])
1
![Page 2: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/2.jpg)
Database Defined
database – an organized collection of related information, used primarily to maintain and look up that information.
relational database -the most common type of database
•Data is organized into tables that have rows and columns.•Thus, a database is a collection of one or more tables
2
![Page 3: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/3.jpg)
Part of a Database Table called Nations
3
Nations
Name Domain Capital Latitude N_S Longitude E_W Interest
Australia AU Canberra 37 S 148 E Beach
Bahamas BS Nassau 25 N 78 W Beach
Barbados BB Bridgetown 13 N 59 W Beach
Belize BZ Belize 17 N 18 W Beach
Bermuda BM Hamilton 32 N 64 W Beach
Cyprus CY Nicosia 35 N 32 E History
Czech Rep. CZ Prague 51 N 15 E Pilsner
Denmark DK Copenhagen 55 N 12 E History
Falkland IS. FK Stanley 51 S 58 W Nature
Finland FI Helsinki 64 N 26 E Nature
Greenland GL Nuuk 72 N 40 W Nature
Iceland IS Reykjavik 65 N 18 W Geysers
Ireland IE Dublin 52 N 7 W History
![Page 4: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/4.jpg)
13 Rows, Tuples, Records
4
Nations
Name Domain Capital Latitude N_S Longitude E_W Interest
Australia AU Canberra 37 S 148 E Beach
Bahamas BS Nassau 25 N 78 W Beach
Barbados BB Bridgetown 13 N 59 W Beach
Belize BZ Belize 17 N 18 W Beach
Bermuda BM Hamilton 32 N 64 W Beach
Cyprus CY Nicosia 35 N 32 E History
Czech Rep. CZ Prague 51 N 15 E Pilsner
Denmark DK Copenhagen 55 N 12 E History
Falkland IS. FK Stanley 51 S 58 W Nature
Finland FI Helsinki 64 N 26 E Nature
Greenland GL Nuuk 72 N 40 W Nature
Iceland IS Reykjavik 65 N 18 W Geysers
Ireland IE Dublin 52 N 7 W History
![Page 5: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/5.jpg)
8 Columns, Fields, Attributes
5
NationsName Domain Capital Latitude N_S Longitude E_W Interest
Australia AU Canberra 37 S 148 E Beach
Bahamas BS Nassau 25 N 78 W Beach
Barbados BB Bridgetown 13 N 59 W Beach
Belize BZ Belize 17 N 18 W BeachBermuda BM Hamilton 32 N 64 W Beach
Cyprus CY Nicosia 35 N 32 E History
Czech Rep. CZ Prague 51 N 15 E Pilsner
Denmark DK Copenhagen 55 N 12 E History
Falkland IS. FK Stanley 51 S 58 W Nature
Finland FI Helsinki 64 N 26 E Nature
Greenland GL Nuuk 72 N 40 W Nature
Iceland IS Reykjavik 65 N 18 W Geysers
Ireland IE Dublin 52 N 7 W History
![Page 6: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/6.jpg)
Properties of Database Tables
• A relational database table can be empty.
• Two tables with the same rows, but in a different order, are the same table.
• Two tables with the same columns, but in a different order, are the same table.
• Thus the information in a database can be ordered (i.e. sorted) in any order that you need.
6
![Page 7: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/7.jpg)
Properties of Database Tables: An Empty table
7
Nations
Name Domain Capital Latitude N_S Longitude E_W Interest
![Page 8: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/8.jpg)
Properties of Database Tables:Two views of the same table
8
NationsName Domain Capital Latitude N_S Longitude E_W InterestAustralia AU Canberra 37 S 148 E BeachBahamas BS Nassau 25 N 78 W BeachBarbados BB Bridgetown 13 N 59 W BeachBelize BZ Belize 17 N 18 W BeachBermuda BM Hamilton 32 N 64 W BeachCyprus CY Nicosia 35 N 32 E History
Nations
Name Domain Capital Latitude N_S Longitude E_W Interest
Belize BZ Belize 17 N 18 W Beach
Barbados BB Bridgetown 13 N 59 W Beach
Australia AU Canberra 37 S 148 E Beach
Bermuda BM Hamilton 32 N 64 W Beach
Bahamas BS Nassau 25 N 78 W Beach
Cyprus CY Nicosia 35 N 32 E History
![Page 9: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/9.jpg)
Properties of Database Tables
• Every row of a database table must be unique in some way.
• The field (i.e. column or attribute) for which all rows of the data base table are unique is the primary key of the table.
9
![Page 10: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/10.jpg)
A Database Table called Nations
10
Nations
Name Domain Capital Latitude N_S Longitude E_W Interest
Australia AU Canberra 37 S 148 E Beach
Bahamas BS Nassau 25 N 78 W Beach
Barbados BB Bridgetown 13 N 59 W Beach
Belize BZ Belize 17 N 18 W Beach
Bermuda BM Hamilton 32 N 64 W Beach
Cyprus CY Nicosia 35 N 32 E History
Czech Rep. CZ Prague 51 N 15 E Pilsner
Denmark DK Copenhagen 55 N 12 E History
Falkland IS. FK Stanley 51 S 58 W Nature
Finland FI Helsinki 64 N 26 E Nature
Greenland GL Nuuk 72 N 40 W Nature
Iceland IS Reykjavik 65 N 18 W Geysers
Ireland IE Dublin 52 N 7 W History
![Page 11: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/11.jpg)
Properties of Database Tables
• Field names (i.e. column names, attribute names) should be descriptive.
• Attribute data (i.e. column data, field data) is atomic – Or Indivisible– The value of a field (i.e. column, attribute) can not
be subdivided into several smaller parts– The date attribute is a classic exception
11
![Page 12: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/12.jpg)
A Database Table called Nations
12
Nations
Name Domain Capital Latitude N_S Longitude E_W Interest
Australia AU Canberra 37 S 148 E Beach
Bahamas BS Nassau 25 N 78 W Beach
Barbados BB Bridgetown 13 N 59 W Beach
Belize BZ Belize 17 N 18 W Beach
Bermuda BM Hamilton 32 N 64 W Beach
Cyprus CY Nicosia 35 N 32 E History
Czech Rep. CZ Prague 51 N 15 E Pilsner
Denmark DK Copenhagen 55 N 12 E History
Falkland IS. FK Stanley 51 S 58 W Nature
Finland FI Helsinki 64 N 26 E Nature
Greenland GL Nuuk 72 N 40 W Nature
Iceland IS Reykjavik 65 N 18 W Geysers
Ireland IE Dublin 52 N 7 W History
![Page 13: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/13.jpg)
Database Scheme
• Also called database schema
• It is the collection of all table definitions for a database.
• Each table definition includes– Table name– Fields with data types– Primary key indicated
13
![Page 14: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/14.jpg)
A Table Definition for Nations Table
• Name: Nations• Fields: (with data type and primary key indicated)
Name (Text)Domain (Text)Capital (Text)Latitude (Text)N_S (Number)Longitude (Text)E_W (Number)Interest (Text)
14
![Page 15: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/15.jpg)
Physical Database
The physical database
• It is made up of all of the physical tables.
• Major rule: No Redundancy!
15
![Page 16: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/16.jpg)
Nations is a Physical Table
16
Nations
Name Domain Capital Latitude N_S Longitude E_W Interest
Australia AU Canberra 37 S 148 E Beach
Bahamas BS Nassau 25 N 78 W Beach
Barbados BB Bridgetown 13 N 59 W Beach
Belize BZ Belize 17 N 18 W Beach
Bermuda BM Hamilton 32 N 64 W Beach
Cyprus CY Nicosia 35 N 32 E History
Czech Rep. CZ Prague 51 N 15 E Pilsner
Denmark DK Copenhagen 55 N 12 E History
Falkland IS. FK Stanley 51 S 58 W Nature
Finland FI Helsinki 64 N 26 E Nature
Greenland GL Nuuk 72 N 40 W Nature
Iceland IS Reykjavik 65 N 18 W Geysers
Ireland IE Dublin 52 N 7 W History
![Page 17: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/17.jpg)
Logical Database and Queries
• It is made up of all of the logical tables.• A logical table is created as the result of a
query.• A query is an operation on one or more
database tables that pulls info from the database.
• For complex queries, go beyond "drag and drop" with Structured Query Language (SQL)
17
![Page 18: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/18.jpg)
Query 1 us a Logical Table
18
Query1
Name Domain
Bahamas BS
Barbados BB
Belize BZ
Bermuda BM
Created by a Query on the Nations Table
![Page 19: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/19.jpg)
Creating Queries (i.e. logical tables)
Steps to create each query…1.Select the table or tables with the needed
data2.Select the fields you wish to see3.Set the criteria for which rows you want4.Sort as prescribed
19
![Page 20: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/20.jpg)
Entity Relationship diagrams
• Also called ER diagrams
• Show the relationships between the physical tables in the physical database.
20
![Page 21: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/21.jpg)
ER Diagram
21
![Page 22: Chapter 16 (p481 – 485, 496-501) Fluency with Information Technology 4 th edition by Lawrence Snyder (slides by Deborah Woodall : woodall@mc.edu) 1](https://reader036.vdocuments.us/reader036/viewer/2022062308/56649de85503460f94ae30fe/html5/thumbnails/22.jpg)
ER Diagram: ∞ to 1
22