1 / 45

Lecture 5

Lecture 5. Lesson Objectives. You will Understand the distinctions among flat-file databases (DBs), network DBs, hierarchical DBs, relational DBs, and text-oriented DBs

jelani-mcfadden
Download Presentation

Lecture 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture 5

  2. Lesson Objectives • You will • Understand the distinctions among flat-file databases (DBs), network DBs, hierarchical DBs, relational DBs, and text-oriented DBs • Become aware that relational DBs have a characteristic known as the level of normalization and that the secret of attaining a satisfactory level of normalization in a DB lies in making its tables simple. • Become aware that modern relational DBs use a standardized language for commands, known as Structured Query Language (SQL) • Understand that multiuser databases face problems of security and privacy Prepared by:Natalie Rose

  3. The world is full of files of data and information, which databases can help you find and manage. Prepared by:Natalie Rose

  4. Examples of Databases • What does the word "database" mean? • Nowadays we usually think it means a computer-stored set of information • However, databases can exist in many forms. Examples: • Electronic data: text, visual images, audio images, numbers • Sheets of paper in folders in a vertical file • A book (think of it as a collection of sentences and illustrations) • Books in a collection (e.g., a library) • Sets of 3"x5" cards containing notes • Blueprints • Maps and other forms of geographic information systems • Core samples from oil wells • Blood samples in a medical laboratory • DNA samples in a forensic laboratory Prepared by:Natalie Rose

  5. Examples of Databases • What do these examples have in common? • Sets of data and information composed of, and/or represented by: bits; or alphanumeric symbols; or lines and shapes in drawings, pictures, and maps; or audio recordings; or video recordings; or realia (i.e., actual substances) • At least one means by which the sets of data and information are organized in order to facilitate access to individual desired sets Prepared by:Natalie Rose

  6. Examples of Databases • Consider the provisions for access to individual pieces of information in the following examples: • Phone book. Contains a collection of several independent (discrete) databases, each consisting of names together with corresponding phone numbers: • White-pages personal listings, arranged alphabetically by surname and within surname by first names • White-pages corporate listings, arranged alphabetically • Blue-pages governmental listings: primary arrangement alphabetical by type of government (city, county, state, federal), secondary arrangement alphabetical by agency within type of government, tertiary arrangement alphabetical by office within agency • Yellow-pages listings: primary arrangement by type of business, secondary arrangement alphabetically by company within type of business, plus various special groupings (e.g., restaurants by ethnic type) Prepared by:Natalie Rose

  7. Examples of Databases • Provisions for access to individual pieces of information, cont'd: • Organizational membership directory, usually consisting of names together with corresponding postal addresses, telephone numbers, and email addresses • Typically contains listings by surname and first names, plus groupings by regions (e.g., states, countries) and by membership in special interest groups (SIGs), arranged alphabetically by name within regional groups and SIGs • Dictionary, consisting of words with corresponding definitions, and in some cases, lists of synonyms and/or antonyms • Primary collection is individual words arranged alphabetically • May contain separate sections (e.g., geographical names, biographical names, abbreviations, proofreaders' marks) Prepared by:Natalie Rose

  8. Examples of Databases • Provisions for access to individual pieces of information, cont'd: • Thesaurus • Primary arrangement by broad concepts (themes), with subgroupings of sets of closely related words (often arranged by type of speech, e.g., nouns, verbs), each set sharing a subconcept of the primary concept; sometimes includes antonyms of the primary concept and/or selected subconcepts • Book (non-fiction) • Table of contents • Provides access to chapters (and sometimes to subchapters) dealing with broad topics that are aspects of the overall subject(s) of the book • Index • Organizes narrow concepts by names, terms, subterms, etc. • Provides pointers from terms to relevant locations in text of book Prepared by:Natalie Rose

  9. Examples of Databases • The foregoing examples illustrate some ways of organizing information in DBs, whether computerized or non-computerized: viz., • An intrinsic index provides information organized by and with the entry or record; or • A separate index can point to the location of the information; or • Records (i.e., basic packages of information) can contain retrieval tags (access tags, labels, etc.) that identify them and that can be searched for; or • (Worst Case) Records can be sought via exhaustive search (by humans or computer programs) Prepared by:Natalie Rose

  10. Computerized Databases • Consist of • Bits, organized into bytes, which in turn are organized into sequences or strings of bytes • Fields: sets of bytes that represent information • Records: sets of fields that are associated by sharing relevance to some entity • Files: sets of records sharing relevance to a particular type of entity • Databases typically consist of one or more sets of related files Prepared by:Natalie Rose

  11. Computerized Databases • Of special interest are Relational Databases (RDBs) and programs that manage them, known as Relational Database Management Systems (RDBMSs). • Note: The word "relational" is often omitted nowadays, since almost all well known DBMSs (e.g., IBM DB2, MS Access, MS SQL Server, Oracle, Sybex) are RDBMSs. Prepared by:Natalie Rose

  12. Introduction to RDBs • In discussing relational databases, we use synonymously the words • File, table, relation • Record and row • Field, column, attribute • Note: Discussions of RDB theory tend to prefer the words italicized above Prepared by:Natalie Rose

  13. Introduction to RDBs • Definition: • Arelational database is a set of one or more tables that together embody information about a set of related concepts and entities. • If, as is usually the case, a relational database has more than one table, the tables are connected (related) in the following way: • It is possible to move from any one table in the RDB to any other table in the RDB via a chain of columns (i.e., fields, attributes) shared in pairwise fashion by successive tables. Prepared by:Natalie Rose

  14. Introduction to RDBs • The picture below shows 3 tables, with a total of 12 attributes (i.e., 12 distinct columns). The top and middle tables share Attribute 3; the middle and bottom tables share Attribute 7. Prepared by:Natalie Rose

  15. Database Applications • The word "application" has a special technical meaning in the field of database management. • Definition: • A databaseapplication is a combination of • A relational database-management system (RDBMS) • A relational database (RDB) • Associated menus, data-entry forms, and report forms • Documentation (e.g., manuals) for the users. Prepared by:Natalie Rose

  16. Database Applications • A database application is a package designed to facilitate a particular real-world function (or a set of related functions): e.g., looking up books in a library catalog, or handling a sales transaction in a store. • Note: An application may include more than one RDB, and/or it may include a "stray" table or two, so long as such additions serve the basic function and make the whole package more convenient for humans to use. Prepared by:Natalie Rose

  17. Other Types of DBs • Types of Databases (other than RDBs) • Flat-file (spreadsheet) • Hierarchical • Network • Text-oriented • Geographic information systems Prepared by:Natalie Rose

  18. Flat File DBs • Flat-file DBs are like the DBs you can construct in a spreadsheet, i.e., all the information in the DB is in one file consisting of one array of rows and columns. For example: Prepared by:Natalie Rose

  19. Flat File DBs • Flat-file databases (spreadsheet style) • Advantages • Simple • Suitable for small numbers of records with few attributes Prepared by:Natalie Rose

  20. Flat File DBs • Flat-file databases (spreadsheet style) • Disadvantages • Likely to include repetitions of data • Multi-valued attributes (e.g., multiple authors, multiple phone numbers) require repetitions of accompanying data (see the "Fulano" entries in the example below) • Changes in data are difficult to implement • Deletion and insertion anomalies are common • Often lead to too much information in one table Prepared by:Natalie Rose

  21. Hierarchical DBs • Hierarchical databases • Are based on a classification scheme (a taxonomy). Any element (node) in the database is linked only to the elements directly above it and directly below it. • Typically require custom programming • The original computer-based databases were designed for banking. Hierarchical databases were appropriate for such purposes: e.g., individual accounts can be grouped by family or business; sets of accounts, grouped by branch; accounts in different branches, grouped by city; accounts in different cities, grouped by state. Prepared by:Natalie Rose

  22. Hierarchical DBs Prepared by:Natalie Rose

  23. Network DBs • Network databases • Permit links among all components, without restrictions to hierarchical levels; i.e., elements can be linked to other elements anywhere in the database, not just those directly above and below • Can be extremely difficult to manage • The World-Wide Web is a very large example of a network database. Prepared by:Natalie Rose

  24. Network DBs Prepared by:Natalie Rose

  25. Text-Oriented DBs • Text-oriented DBs are, as their name suggests, DBs that have special features for handling text: e.g., abilities • To search for specified strings of characters • With or without matching the cases of the characters • While using wildcards, i.e., symbols that will match any one character or any sequence of characters • To search on pairs, triples, etc., of words and phrases, using • Boolean logic • Proximity logic (e.g. both words must be in same sentence, or in same paragraph, or in same section, or within n words of each other) Prepared by:Natalie Rose

  26. Text-Oriented DBs • Abilities of text-oriented DBs, cont'd • To rank search results by weights assigned to the terms used in the search • To maintain thesauri of near synonyms and to allow searches by near synonyms of original query terms • To maintain, for selected words or phrases, indexes of their locations in files • Although commercial text-oriented DBs exist (e.g., Lexis-Nexis and Dialog), the only text-oriented DBMSs for microcomputers that I know of are askSam, DB/Textworks, and Isys. (From personal use, I can give askSam high marks for power and ease of use.) Prepared by:Natalie Rose

  27. Geographic Information Systems • Geographic information systems (GISs) manage data based on geographic coordinates, usually obtained through the use of the Geographic Positioning System (GPS). • GISs typically integrate a variety of data relevant to such concerns as: atmosphere, ecology, environment, geology, natural resources, pollution, transportation, utilities, and characteristics and behaviors of populations (e.g., socioeconomic status, crimes, diseases, vehicular traffic). • The integrated data are displayed through the use of maps, which may include many overlapping, partly transparent layers. Prepared by:Natalie Rose

  28. Advantages of RDBs • Advantages of relational databases • Cut down on needless repetition of information • Ensure more accuracy • Facilitate updating and deletion of information. • Design avoids problems that occur with flat files, e.g., insertion and deletion anomalies • "Anomalies" are errors that occur when information is added to or deleted from existing records, or when whole records are added to or removed from a DB. Prepared by:Natalie Rose

  29. Relational Databases • In a RDB, the information content of a table does notdepend on either • The order of the rows; or • The order of the columns • In other words, the rows and columns of a table can be rearranged at will without affecting the table's information content Prepared by:Natalie Rose

  30. Primary Keys • In a RDB, each table • Must have a primary key (unique identifier) • Must have no duplicate rows • A primary key is • A data attribute (column), or a combination of attributes, that uniquely identifies each record in the table. • A simple key consists of a single attribute • A composite key consists of two or more attributes Prepared by:Natalie Rose

  31. Primary Keys • Primary Key • Provides unique way to identify each record • Can be obvious from the structure of the table. If there is no easy natural choice, you can add a column containing a unique identifier. • May consist of the entire record (especially with two-column tables, which occur often in the development of RDBs) Prepared by:Natalie Rose

  32. Other Types of Keys • Secondary Key • A column that is used to aid in the retrieval of information from a table. A secondary key is not required to have unique values in each of its rows, • Foreign Key • A column that is used to aid in the retrieval of information from one table (i.e., is a secondary key) and that is also the primary key in another table. Foreign keys are a major tool in RDBs. • All foreign keys are secondary keys, but not all secondary keys are foreign keys. Prepared by:Natalie Rose

  33. Types of Fields • Character • Numeric • Logical • Image • Object • LBO (Large Binary Object) Prepared by:Natalie Rose

  34. Types of Fields • Character: Alphanumeric and punctuation. Character fields can include numerals, but calculations cannot be performed on the contents of the field even though they look like numbers. • Examples: Names, product codes, SSNs, ZIPCodes • Sometimes further differentiated between memo fields (short) and text fields (long) Prepared by:Natalie Rose

  35. Types of Fields • Numeric: Arithmetic operations can be performed on the contents of the field • Numbers (sometimes further differentiated among integers [short, long], fixed-decimal point numbers, floating-point numbers) • Dates (addition and subtraction only) Prepared by:Natalie Rose

  36. Types of Fields • General Rule for Numeric Fields:Neverassign a field a numeric data type unless you intend to perform mathematical calculations on its contents. Prepared by:Natalie Rose

  37. Types of Fields • Logical: True-False (T, F), Boolean (0, 1), Yes-No (Y, N) • Image: Used for relatively small graphics files (e.g., under 100KB) • Object: An object is a package of data together with program code that performs certain functions on the data • Large Binary Object: Used for very large items, such as multi-megabyte graphic, audio, and video files. The RDB typically merely stores pointers to the locations of these large files. Prepared by:Natalie Rose

  38. What Is SQL? • Structured Query Language (SQL) • Is pronounced either "S, Q, L" or "sequel" • Is a widely used standard set of commands and syntax for doing things with RDBMSs • Is used especially for query and retrieval • Includes commands for defining RDBs, conducting transactions, storing data, etc. • Is implemented in all major RDBMSs • SQL does not handle all the practical details involved in using a RDB, so every RDBMS has additional features unique to it Prepared by:Natalie Rose

  39. History of SQL • A prototype DB query and retrieval language was developed by IBM in the early 1970s as "Sequel" • Other RDB developers and users recognized the advantage of a standard language for manipulating RDBs • This led to ANSI adoption of SQL as an enhanced RDB language based on Sequel Prepared by:Natalie Rose

  40. Multiuser DBs • You are probably accustomed to using large Internet-based DBs as well as a DBMS on an individual microcomputer. In the latter case you are the only user of the DBMS and the DBs it handles, and you are also the manager of the DBMS. • In using a DBMS on an individual computer, you are unlike managers of multiuser DBMSs and DBs, in that you do not have to worry about questions of privacy and security—unless, of course, you are connected to the Internet or allow other people to use your computer. Prepared by:Natalie Rose

  41. Multiuser DBs • You need to become aware that privacy and security are major concerns of multiuser DBMSs and DBs. With such DBMSs and DBs, much time and effort must be devoted to providing protection against violations of privacy and confidentiality, and against outright theft of data. Prepared by:Natalie Rose

  42. Centralized Processing • Processing is performed in one computer or in a cluster of coupled computers in a single location. Centralized processing was the architecture that evolved from the very first computers; however, user access was via dumb terminals that performed none of the primary processing. Today, centralized computers use terminals that are mostly full-featured desktop computers. Prepared by:Natalie Rose

  43. Decentralized Processing • Computer systems in different locations. Although data may be transmitted between the computers periodically, it has limited daily communications. Prepared by:Natalie Rose

  44. Distributed Processing • Distributed is defined as data and programs are spread out over several locations, and computers share the workload. • Client/server system: This is a distributed system in which a server (usually a combination of special hardware and software) provides services to other computer(s) called clients; server and client may both be PCs, and one computer may be a server for some activities and a client for other activities. It also use of multiple computers networked throughout a wide geographical area, or the world via the Internet. Prepared by:Natalie Rose

  45. Computerized Databases— They can help to save you from this kind of work! Prepared by:Natalie Rose

More Related