Databases Demystified- P8

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:11

0
34
lượt xem
3
download

Databases Demystified- P8

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'databases demystified- p8', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: Databases Demystified- P8

  1. Databases Demystified 330 referential constraints, 216–217 conceptual database design, 25, 26, 135–136 triggers, 219–220 attributes, 27–28 unique constraints, 217–218 business rules, 32 entities, 27 C relationships, 28–32 CA-INGRES, 18 concurrent update problem, 279–280 Call Level Interface (CLI), 240 Conference on Data Systems Languages candidates, 152 (CODASYL), 17 Cartesian products, 108–109 consistency, 276 cascading deletes, 39, 41 constraints, 32 cascading updates, 41 CHECK, 43–44, 121, 218 catalog views, 96, 97 defined, 37 centralized deployment model, 228–229 enforcing using triggers, 44–45 CGI, 239 foreign key, 37–41, 216–217 non-CGI gateways, 240 integrity, 42–45 change control, 287–288 naming conventions, 212–213 change request tracking, 287 NOT NULL, 43, 216 CHECK constraints, 43–44, 121, 218 primary key, 37, 120, 216 check-out/check-in, 288 referential, 37–41, 120, 216–217 Chen, Peter, 18, 180 unique, 120–121, 217–218 Chen’s format, 180–181 contention, 281 chicken method, 138 cookies, 257–258 cipher text, 256 correlated subselects, 107 circuit-level gateways, 251 CREATE INDEX statements, 121–122 class hierarchy, 16 CREATE TABLE statements, 118–119 client tools, 91–92 CREATE VIEW statements, 121 client/server deployment model CRUD matrix, 196–197 N-tier, 234–235 cursor processing, 273–275 three-tier, 233–234 Customers table, listing entire, 62–63 two-tier, 231–233 Codd, E.F., 13, 17–18, 136 D and normalization, 145 Data Control Language (DCL), 90, 122–124 columns, 34–36 Data Definition Language (DDL), 90, 118–122 calculated, 77–80 data encryption, 256–257 choosing to display, 63–64 data flow diagram, 194–196 functions, 81 data integrity, 214 limiting columns to display, 100–101 Data Manipulation Language (DML), 90, naming conventions, 212 114–118 COMMIT statements, 114–115 tuning DML statements, 286 Common Gateway Interface, 239 data marts, 301–302 non-CGI gateways, 240 data mining, 302–303 comparison operators, 70, 71 data modelers, 25 Computer Books Company (CBC), 170–173 Data Query Language (DQL), 90 SELECT statements, 100–114
  2. INDEX 331 data stores, 194–195 DBMS. See Database Management System data types, 34–36, 218–219 deadlocks, 282–283 data warehousing, 293–294 See also locking mechanisms challenges to creating, 295 delete anomaly, 148 defined, 294 DELETE statements, 117–118 multidimensional databases, 299–301 tuning, 286 snowflake schema, 299 deliverables, 129 star schema architecture, 297–299 denial of service attacks, 251 starflake schema, 299 denormalization, 163–164 summary table architecture, 296–297 See also normalization database administrators. See DBAs dependent entities, 184 database clients and application security, 255–258 deployment models database design centralized model, 228–229 conceptual, 25, 26–32 client/server model, 231–235 logical, 25, 33–46 distributed model, 229–230 physical, 26, 33–46 DFD. See data flow diagram database designers, 25–26 dimension tables, 298 Database Management System, 2–3 distributed deployment model, 229–230 catalog, 6 document review, 135 database models domain-key normal form (DKNF), 163 defined, 2 DROP statements, 122 flat files, 7–9 durability, 276 hierarchical model, 9–11 network model, 11–13 E object-oriented model, 15–16 Ellison, Larry, 18 object-relational model, 16–17 encapsulation, 15 relational model, 13–15 encryption, 256–257 database objects entities, 27 defined, 2, 15 relating, 196–197 finding using catalog views, 97 entity-relationship diagrams, 14, 18, 180 viewing using Oracle Enterprise Manager, Chen’s format, 180–181 98–99 formats, 180–184 databases guidelines, 188–189 connecting to applications, 240–242 IDEF1X format, 182–184, 185–186 connecting to the Web, 235–240 relational format, 181–182 defined, 1–2 entity-relationship modeling, 180 history of, 17–19 Epstein, Bob, 19 life cycle, 129–130 ERDs. See entity-relationship diagrams properties of, 1–7 exam, final, 307–324 security, 260 explicit mode, 277 DB2, 18, 91 expressions, 100–101 DBAs, 4 Extensible Markup Language, 238
  3. Databases Demystified 332 external design, 135 HP ALLBASE, 18 external entities, 27 Hypertext Markup Language (HTML), 238 external layer, 5 Hypertext Transfer Protocol (HTTP), 237 See also layers of abstraction external model, 5 I extranets, 237 IBM, 17, 90–91 IDEF1X format, 182–184, 185–186 F identifying relationships, 183 fact tables, 298 IDMS/SQL, 18 fifth normal form, 163 Illustra, 19 files, 2 implicit mode, 277, 278 See also flat files independence final exam, 307–324 logical data, 6–7 firewalls, 237, 251 physical data, 5–6 first normal form, 153–155 indexes, 37 flat files, 7–9 adding for performance, 221–222 See also database models index selectivity, 285 flowcharts, 190–192 naming conventions, 213 foreign key constraints, 37–41, 216–217 Information Management System (IMS), 17 naming, 189 Informix, 19 forms, 55 INGRES DBMS, 18, 91 forms-based queries, 52 Inmon, William H., 293, 296 fourth normal form, 162–163 inner joins, 72–74 functional dependence, 156 of two tables, 109 functions insert anomaly, 148 aggregate, 80–82, 83, 112–114 INSERT statements, 115–116 column, 81 tuning, 286 function hierarchy diagram, 192–193 instances, 27, 261 defined, 1 G See also object instances Gane, Chris, 194 Integrated Data Store (IDS), 17 General Electric, 17 integrity constraints, 42–45 Generalized Update Access Method (GUAM), 17 internal design, 136 GRANT statements, 123 Internet grantees, 122 Internet computing model, 234–235 GROUP BY clause, aggregate functions with the, isolating the enterprise network from the, 250 112–114 overview, 236–238 intersection data, 31 H intersection tables, 41–42 Hawthorne effect, 135 interviews, conducting, 133–134 hierarchical model, 9–11 intranets, 237 See also database models IP spoofing, 251 host language variables, 275 isolation, 276
  4. INDEX 333 J tables, 33–34 Java applications, connecting databases to, using normalization, 145–173 241–242 views, 45–46 Java Database Connectivity, 241–242 logical data independence, 6–7 Java Relational Binding, 242 logical layer, 5 Java SQL, 242 See also layers of abstraction JDBC, 241–242 login joining, 14 credentials, 256 inner joins, 72–74, 109 security, 259–260 limiting join results, 72–74, 110–111 multiple joins, 77–80 M outer joins, 75–77, 78 MAC address lists, 254 outer joins in Oracle, 110 macros, 55 self-joins, 82–84, 110 many-to-many relationships, 31 tables, 70–72, 73, 108–111 mapping, 25 Joint Application Design (JAD), 140 direct mapping of RDBMS tables to Java JRB, 242 classes, 242 JSQL, 242 master database, 260–261 maximum cardinality, 28, 183–184 K metadata, 7 Kimball, Ralph, 297 methods, defined, 15 King, Frank, 18 Microsoft Access advanced sorting, 66, 67 L aggregate functions, 80–82, 83 layers of abstraction, 3, 4 calculated columns, 77–80 external layer, 5 choosing columns to display, 63–64 logical layer, 5 choosing rows to display, 66–68 physical layer, 4 compound row selection, 68–69 life cycle, 129–130 creating queries in, 59–85 nontraditional methods, 139–140 Datasheet View, 57 prototyping, 139–140 Design View, 57–59 system development life cycle (SDLC), getting started in, 52–55 130–139 joining tables, 70–72, 73 LIKE operator, 104–105 limiting join results, 72–74 limiting columns to display, 100–101 listing entire Customers table, 62–63 limiting join results, 110–111 multiple joins, 77–80 listing all employees, 100, 101 outer joins, 75–77, 78 locking mechanisms, 280–281 Queries window, 59 deadlocks, 282–283 Query Design View panel, 60–62 logical database design, 25, 136 Relationships panel, 55–57 columns and data types, 34–36 self-joins, 82–84 constraints, 37–42 sorting results, 64–65 integrity constraints, 42–45 using not equal, 70, 71
  5. Databases Demystified 334 Microsoft SQL Server TLA University academic tracking, 164–170 database security in, 259–261 See also denormalization system privilege examples, 264 North American Aviation (NAA), 17 transaction support in, 277–278 not equal, 70, 71 middleware, 242 NOT NULL constraints, 43, 216 minimum cardinality, 28, 183–184 model database, 261 O models, database, 2 object instances, 16 modules, 55 object privileges, 123, 261, 265 MOLAP databases, 299–301 object request brokers, 230 msdb database, 261 object-oriented model, 15–16 multidimensional OLAP databases, 299–301 See also database models multiple joins, 77–80 object-relational model, 16–17 multivalued attributes, 150 See also database models objects, defined, 2, 15 N observation, 134–135 namespace, 100, 213 ODBC, 240–241 naming conventions OEM, 98–99 columns, 212 OLAP, 294 constraints, 212–213 OLTP, 294 indexes, 213 compared with data warehouse systems, 295 tables, 211 one-to-many relationships, 30 views, 213–214 in a hierarchical model, 10 natural identifiers, 151 one-to-one relationships, 28–29 network address translation (NAT), 252 online analytical processing. See OLAP network computing devices, 235 online transaction processing. See OLTP network model, 11–13 Open Database Connectivity, 240–241 See also database models operators, 70, 71 network security, 250 BETWEEN, 104 isolating the enterprise network from the LIKE, 104–105 Internet, 250–253 OR, 105, 106 securing wireless network access, 253–254 OR. See object-relational model nodes, 10 OR operator, 105, 106 non-CGI gateways, 240 Oracle, 18 noncorrelated subselects, 107 database security in, 261–263 non-identifying relationships, 183 Enterprise Manager, 98–99 nonprocedural languages, 191 outer joins, 110 normalization, 33, 136 Personal Edition, 89–90 applying, 148–163 SQL, 91–96 Computer Books Company (CBC), 170–173 system privilege examples, 264–265 logical database design using, 145–173 transaction support in, 278 need for, 147–148 outer joins, 75–77, 78 practice problems, 164–173 in Oracle, 110 process, 146 owner-member relationships, 12
  6. INDEX 335 P Q packets, 250 QBE, 52 filtering, 251 queries, 55 pages, 55 creating in Microsoft Access, 59–85 parent-child relationships, 12–13 Query By Example. See QBE partitioning, 33 query execution plan, 284 performance tuning, 283–284 query languages, 3 tuning database queries, 284–285 quizzes permissions, 261 answers, 325–327 See also privileges Chapter 1, 20–23 persistence, 276 Chapter 2, 46–49 physical database design, 26, 136–137, 203 Chapter 3, 85–88 adding indexes for performance, 221–222 Chapter 4, 124–127 columns and data types, 34–36 Chapter 5, 141–144 constraints, 37–42 Chapter 6, 174–177 designing tables, 204–214 Chapter 7, 198–201 designing views, 220–221 Chapter 8, 222–226 implementing super types and subtypes, Chapter 9, 242–246 208–210 Chapter 10, 268–271 integrating business rules and data integrity, Chapter 11, 288–291 214–220 Chapter 12, 303–306 integrity constraints, 42–45 tables, 33–34 R views, 45–46 Rapid Application Development (RAD), 140 physical data independence, 5–6 RDBMSs. See Relational Database Management physical layer, 4 Systems See also layers of abstraction record types, 9 physical security, 249–250 records, 10 pointers, 10 recursive relationships, 31–32 port scans, 252 Red Brick, 297–298 precision, 218–219 referential constraints, 37–41, 120, 216–217 primary key constraints, 37, 120, 216 referential integrity, 41 choosing a primary key, 151–153 relational calculus, 18 naming, 189 Relational Database Management Systems, 15 prioritization of changes, 287 relational format, 181–182 private keys, 256 relational model, 13–15 privileges, 122–123, 261, 263 features of, 19 procedural languages, 191 See also database models process models, 189–196 relationships, 28 processes, relating, 196–197 many-to-many, 31 prototyping, 139–140 Microsoft Access Relationships panel, 55–57 proxy servers, 251–252 one-to-many, 10, 30 public keys, 256 one-to-one, 28–29 recursive, 31–32
  7. Databases Demystified 336 release numbering, 287 views, 266–267 repeating groups, 153–155 Web browser security level, 257–258 reports, 55 SELECT statements, 100–114 result sets, 273 self-joins, 82–84, 110 REVOKE statements, 123–124 server privileges, 261 roles, 95, 123, 265–266 servers, 55 ROLLBACK statements, 114–115 Server-Side Includes, 239 routers, 250 sets, 12 routing tables, 250 Slammer worm, 247–248 rows sorting choosing to display, 66–68, 103–108 advanced, 66, 67 compound row selection, 68–69 results, 64–65, 102 SQL, 40 S aggregate functions, 112–114 Sarson, Trish, 194 ALTER TABLE statements, 119–121 scale, 218–219 BETWEEN operator, 104 schemas, 262–263 Cartesian products, 108–109 defined, 2 choosing rows to display, 103–108 logical layer, 5 COMMIT and ROLLBACK statements, owner accounts, 263–264 114–115 subschemas, 5 compound conditions using OR, 105, 106 scripting languages, 258 CREATE INDEX statements, 121–122 second normal form, 156–158 CREATE TABLE statements, 118–119 security CREATE VIEW statements, 121 architectures, 259–263 DELETE statements, 117–118 cookies, 257–258 DROP statements, 122 data encryption, 256–257 embedded SQL for Java, 242 database access, 258–267 GRANT statements, 123 database clients and application security, history of, 90–91 255–258 injection, 258 login credentials, 256 inner joins of two tables, 109 in Microsoft SQL Server, 259–261 INSERT statements, 115–116 monitoring and auditing, 267 LIKE operator, 104–105 need for, 247–248 limiting columns to display, 100–101 network, 250–254 limiting join results, 110–111 object privileges, 265 listing all employees, 100, 101 in Oracle, 261–263 Oracle, 91–96 physical, 249–250 outer joins in Oracle, 110 roles, 265–266 REVOKE statements, 123–124 schema owner accounts, 263–264 self-joins, 110 scripting languages, 258 simple WHERE clause, 103 in Sybase, 259–261 sorting results, 102 system privileges, 264–265 statements, 90 system-level, 255
  8. INDEX 337 subselects, 106–108 ongoing support, 138–139 tuning tips, 284–285 physical database design, 136–137 UPDATE statements, 116–117 planning, 130–132 See also Java SQL requirements gathering, 132–135 SQL Plus, 91–96 system privileges, 122, 261, 264–265 SQL Plus Worksheet, 91, 93–96 System R, 18, 90 SQL Server. See Microsoft SQL Server system-level security, 255 SQL/DS, 18 SSI, 239 T star schema data warehouse architecture, tables, 33–34, 54 297–299 designing, 204–214 state, 184 dimension, 298 statement privileges, 261 fact, 298 Stonebraker, Michael, 18, 19 intersection, 41–42 subclasses, 184 joining, 70–72, 73, 108–111 subqueries, 106–108 listing entire Customers table, 62–63 INSERT statements with, 116 naming conventions, 211 subschemas, 5 virtual, 45 subselects, 106–108 tablespaces, 33 subtypes, 184–188 technology stack components, 238 collapsing into the super type table, 210 tempdb database, 261 implementing as discrete tables, 208–210 terminal emulators, 229 implementing as is, 208, 209 third normal form, 158–160 summary table data warehouse architecture, beyond, 160–163 296–297 TLA University academic tracking, 164–170 super classes, 184 transactions super types, 184–188, 208–210 ACID, 276 surrogate identifiers, 151 concurrent update problem, 279–280 surveys, conducting, 134 DBMS support for, 276–278 swim lane diagram, 193–194 defined, 276 Sybase, 18–19 log, 277–278 database security in, 259–261 support in Microsoft SQL Server, 277–278 symmetric keys, 256 support in Oracle, 278 synonyms, 100, 213 transitive dependencies, 158–160 System 10, 18–19 triggers, 219–220 system development life cycle (SDLC), 130 constraint enforcement using, 44–45 conceptual database design, 135–136 conducting interviews, 133–134 U conducting surveys, 134 Uniform Resource Locators. See URLs construction, 137–138 URL spoofing, 258 document review, 135 unique constraints, 120–121, 217–218 implementation and rollout, 138 unique identifiers, 27, 151–152 logical database design, 136 universal databases, 16 observation, 134–135 See also object-relational model
  9. Databases Demystified 338 update anomaly, 148 virtual tables, 45 UPDATE statements, 116–117 virus scanners, 258 tuning, 286 URLs, 237 W spoofing, 258 Web user views, 3, 133 browser security level, 257–258 Computer Books Company (CBC), 170–173 connecting databases to the, 235–240 TLA University academic tracking, 165–167 cookies, 257–258 users, 3, 261, 262 invoking transactions from Web pages, USER_TABLES, 97 239–240 USER_VIEWS, 97 overview, 236–238 technology stack components, 238 V WHERE clause, 103 VALUES clause, INSERT statements with, wildcards, 104–105 115–116 wireless networks, securing access, 253–254 variables Wong, Eugene, 18 defined, 15 World Wide Web. See Web host language variables, 275 version numbering, 287 X views, 45–46, 266–267 XML, 238 catalog views, 96, 97 designing, 220–221 Z naming conventions, 213–214 zombie attacks, 251 virtual private networks (VPNs), 252–253
  10. INTERNATIONAL CONTACT INFORMATION AUSTRALIA SOUTH AFRICA McGraw-Hill Book Company McGraw-Hill South Africa Australia Pty. Ltd. TEL +27-11-622-7512 TEL +61-2-9900-1800 FAX +27-11-622-9045 FAX +61-2-9878-8881 robyn_swanepoel@mcgraw-hill.com http://www.mcgraw-hill.com.au books-it_sydney@mcgraw-hill.com SPAIN McGraw-Hill/ CANADA Interamericana de España, S.A.U. McGraw-Hill Ryerson Ltd. TEL +34-91-180-3000 TEL +905-430-5000 FAX +34-91-372-8513 FAX +905-430-5020 http://www.mcgraw-hill.es http://www.mcgraw-hill.ca professional@mcgraw-hill.es GREECE, MIDDLE EAST, & AFRICA UNITED KINGDOM, NORTHERN, (Excluding South Africa) EASTERN, & CENTRAL EUROPE McGraw-Hill Hellas McGraw-Hill Education Europe TEL +30-210-6560-990 TEL +44-1-628-502500 TEL +30-210-6560-993 FAX +44-1-628-770224 TEL +30-210-6560-994 http://www.mcgraw-hill.co.uk FAX +30-210-6545-525 emea_queries@mcgraw-hill.com MEXICO (Also serving Latin America) ALL OTHER INQUIRIES Contact: McGraw-Hill Interamericana Editores McGraw-Hill/Osborne S.A. de C.V. TEL +1-510-420-7700 TEL +525-1500-5108 FAX +1-510-420-7703 FAX +525-117-1589 http://www.osborne.com http://www.mcgraw-hill.com.mx omg_international@mcgraw-hill.com carlos_ruiz@mcgraw-hill.com SINGAPORE (Serving Asia) McGraw-Hill Book Company TEL +65-6863-1580 FAX +65-6862-3354 http://www.mcgraw-hill.com.sg mghasia@mcgraw-hill.com
Đồng bộ tài khoản