Lesson 2: Data Structures PDF

Lesson 2 uniformly throughout the organization  Data Structures Better security - The DBA A framework for org...

Lesson 2 uniformly throughout the organization  Data Structures Better security - The DBA A framework for organizing, storing, and ensures that only legitimate managing data users access the database and Consists of files or tables that interact in various different users have different ways levels of access Each file or table contains data about people, Data independence - Systems places, things, or events that interact with a DBMS are relatively independent of how the physical data is maintained Is File Processing Still Important? That design provides the DBA flexibility to Handles large volumes of alter data structures structured data on a regular without modifying basis information systems Can be cost-effective that use the data Great for transaction processing DBMs CoMponents The Database Environment InterfaCes for Users, A database management system DataBase aDMInIstrators, anD (DBMS) is a collection of tools, features, and interfaces that reLateD systeMs enables users to add, update, USERS manage, access, and analyze data Typically work with predefined queries and DBMS Advantages switchboard commands, Scalability - A system can be but also use query expanded, modified, or languages to access downsized stored data Economy of scale - Database DATABASE design allows better utilization ADMINISTRATORS of hardware Concerned with data Enterprise-wide application - A security and integrity, database administrator (DBA) preventing unauthorized assesses overall requirements access, providing and maintains the database for backup and recovery, the entire audit trails, maintaining the database, and Stronger standards - Standards supporting user needs for data names, formats, and documentation are followed RELATED INFORMATION SYSTEMS  A DBMS can support data and interpret client requests several related in HTML form; then translate information systems the requests into commands that that provide input to, the database can execute and require specific data Data Security from, the DBMS Web-based data must be secure, yet easily accessible to Data Manipulation Language authorized users A data manipulation language Definitions: (DML) controls database ENTITY operations, including storing, retrieving, updating, and An entity is a person, deleting data place, thing, or event for which data is collected Schema and maintained The complete definition of a TABLE OR FILE database, including descriptions of all fields, tables, and A table, or file, contains relationships, is called a schema a set of related records that store data about a Physical Data Repository specific entity The complete definition of a FIELD database, including descriptions of all fields, tables, and A field, also called an relationships, is called a schema attribute, is a single characteristic or fact about an entity WeB Data BaseD DesIgn RECORD Overview A record, also called a tuple (rhymes with A data manipulation language couple), is a set of (DML) controls database related fields that operations, including storing, describes one instance, retrieving, updating, and or occurrence, of an deleting data entity, such as one Connecting to the Web customer, one order, or one product The objective is to connect the database to the Web and enable Key Fields: data to be viewed and updated PRIMARY KEY Middleware - software that A field or combination integrates different applications of fields that uniquely and allows them to exchange and minimally identifies  a particular member of Types of Relationships an entity Three types of relationships can CANDIDATE KEY exist between entities: Any field that could One-to-one serve as a primary key One-to-many is called a candidate key FOREIGN KEY Many-to-many A common field that exists in more than one table and can be used to A one-to-one relationship, abbreviated form a relationship, or 1:1, exists when exactly one of the link, between the tables second entity occurs for each instance of the first entity SECONDARY KEY A one-to-many relationship, abbreviated A field or combination 1:M, exists when one occurrence of the of fields that can be first entity can relate to many instances used to access or of the second entity, but each instance of retrieve records the second entity can associate with only one instance of the first entity Referential Integrity: A many-to-many relationship, A set of rules that avoids data abbreviated M:N, exists when one inconsistency and quality instance of the first entity can relate to problems. In a relational many instances of the second entity, and database, referential integrity one instance of the second entity can means that a foreign key value relate to many instances of the first cannot be entered in one table entity unless it matches an existing primary key in another table CarDInaLIty entIty reLatIonshIp Describes the numeric DIagraM relationship between two entities and shows how instances of one entity relate to instances Drawing an ERD of another entity The first step is to list the A common method of entities that you identified cardinality notation is during the systems analysis called crow’s foot phase and to consider the nature notation because of the of the relationships that link shapes, which include them circles, bars, and symbols,  that indicate various Normalization involves applying a set of possibilities rules that can help you identify and correct inherent problems and complexities in your table designs The normalization process typically involves four stages: Unnormalized design First normal form Second normal form Third normal form Second Normal Form (2NF) First Normal Form (1NF) Must understand the concept of A table is in first normal form functional Dependence (1NF) if it does not contain a repeating group Field A is functionally dependent on Field B if When you eliminate the the value of Field A repeating group, additional depends on Field B records emerge — one for each combination of a specific order A DATE value is and a specific product functionally dependent on an ORDER, because The result is more for a specific order records, but a greatly number, there can be simplified design only one date Second Normal Form (2NF) Objective is to break the original table into two Must understand the concept of or more new tables and functional Dependence reassign the fields so Field A is functionally that each non-key field dependent on Field B if will depend on the the value of Field A entire primary key in its depends on Field B table A DATE value is functionally dependent on an ORDER, because for a specific order number, there can be Data norMaLIzatIon only one date Normalization is the process of creating Objective is to break the table designs by assigning specific fields original table into two or attributes to each table in the database or more new tables and reassign the fields so  that each non-key field Data Mining will depend on the  Looks for entire primary key in its meaningful table data patterns and Third Normal Form (3NF) relationships in large amounts A design is in 3NF if every non- of data key field depends on the key, the whole key, and nothing but Logical versus Physical Storage the key  Logical storage refers to data A 3NF design avoids that a user can view, understand, redundancy and data integrity and access, regardless of how or problems that still can exist in where that information actually 2NF designs is organized or stored To convert the table to 3NF, you  Physical storage is strictly must remove all fields from the hardware-related because it 2NF table that depend on involves the process of reading another non-key field and place and writing binary data to them in a new table that uses the physical media such as a hard non-key field as a primary key drive, CD-ROM, or network- based storage device  Data storage anD aCCess  Data Coding Tools and Techniques  EBCDIC (Extended Binary Coded Decimal Interchange  Companies use data Code - pronounced EB-see-dik) warehousing and data mining as strategic tools to help manage  A coding method used the huge quantities of data they on mainframe need for business operations and computers and high- decisions capacity servers  Data warehousing  ASCII (American Standard Code for Information  Data mining Interchange - pronounced ASK- ee) Data Warehousing  A coding method used  An integrated on most personal collection of data computers that can include seemingly unrelated  BINARY information, no matter where it  Represents numbers as is stored in actual binary values, the rather than as coded company numeric digits  Data Coding (Cont.)  UNICODE  Supports virtually all languages and has become a global standard  Data Coding (Cont.)  STORING DATES  Y2K Issue  International Organization for Standardization (ISO) requires a format of four digits for the year, two for the month, and two for the day (YYYYMMDD) Data ControL  A well-designed DBMS must provide built-in control and security features, including subschemas, passwords, encryption, audit trail files, and backup and recovery procedures to maintain data