Lecture Notes on INFORMATION RESOURCES

1 Vilnius Gediminas Technical University Jelena Mam enko Lecture Notes on INFORMATION RESOURCES Part I introduction to Dta Modeling and MSAccess Code FMITB02004 Course title INFORMATION Resourses Course volume 3,0 cr. (4,50 ECTS cr.) Teaching methods (Full-time, daytime studies): Lectures - 16 h per semestre Laboratory works - 32 h per semestre Individual work - 72 h per semester Course aim Understandig of models and system of INFORMATION resourses. Jelena Mam enko introduction to Data Modeling and MSAccess Lecture Notes on Informatikon RESOURCES Part I 2 CONTENT 1 introduction to Data Modeling.

2 5 Data Modeling Overview .. 5 Methodology .. 6 Data Modeling In the Context of Database 6 Components of A Data 6 Why is Data Modeling Important? .. 6 Summary .. 7 The Entity-Relationship Model .. 7 Basic Constructs of E-R 7 7 Special Entity 7 2 Data Modeling As Part of Database 8 Requirements 8 Steps In Building the Data Model .. 9 Summary .. 9 3 Identifying Data Objects and Relationships .. 10 10 Attributes .. 11 Validating Attributes .. 11 Derived Attributes and Code Values .. 12 Relationships .. 12 Naming Data Objects .. 13 Object Definition.

3 13 Recording INFORMATION in Design Document .. 14 Summary .. 15 4 Developing the Basic Schema .. 16 Binary Relationships .. 16 16 One-To-Many .. 17 Many-To-Many .. 17 Recursive 17 Summary .. 17 5 Refining The Entity-Relationship Diagram .. 18 Entities Must Participate In Relationships .. 18 Resolve Many-To-Many 18 Transform Complex Relationships into Binary 19 Eliminate redundant relationships .. 20 Summary .. 20 6 Primary and Foreign 21 Define Primary Key 21 Composite Keys .. 21 Artificial Keys .. 22 Primary Key Migration .. 22 Define Key Attributes.

4 22 Validate Keys and 23 Foreign Keys .. 23 Identifying Foreign 23 Jelena Mam enko introduction to Data Modeling and MSAccess Lecture Notes on Informatikon RESOURCES Part I 3 Foreign Key Ownership .. 23 Diagramming Foreign Keys .. 23 Summary .. 24 7 Adding Attributes to the 25 Relate attributes to entities .. 25 Parent-Child 25 Multivalued Attributes .. 25 Attributes That Describe Relations .. 26 Derived Attributes and Code Values .. 26 Including Attributes to the ER 27 Summary .. 27 8 Generalization Hierarchies .. 28 Description.

5 28 Creating a Generalization Hierarchy .. 28 Types of Hierarchies .. 28 Rules .. 29 Summary .. 29 9 Add Data Integrity 30 Entity Integrity .. 30 Referential Integrity .. 30 Insert and Delete 30 Insert 30 Delete Rules .. 30 Delete and Insert Guidelines .. 31 31 Primary Key Domains .. 31 Foreign Key Domains .. 3110 Overview of the Relational Model .. 3211 Data Structure and Terminology .. 33 12 Notation .. 35 13 Properties of Relational Tables .. 35 14 Relationships and Keys .. 37 15 Relational Data Manipulation .. 39 16 Relationships .. 42 Attributes .. 42 Classifying Relationships.

6 42 Degree of a Relationship .. 42 Connectivity and 42 Direction .. 43 Type .. 43 Existence .. 43 Generalization Hierarchies .. 43 ER Notation .. 44 Summary .. 45 17 Normalization .. 46 Basic Concepts .. 46 Functional Dependencies .. 46 Overview .. 46 Sample Data .. 47 Jelena Mam enko introduction to Data Modeling and MSAccess Lecture Notes on Informatikon RESOURCES Part I 4 First Normal 47 Second Normal Form .. 48 Third Normal Form .. 49 18 Advantages of Third Normal Form .. 50 Advanced 50 Boyce-Codd Normal Form .. 50 Fourth Normal Form.

7 50 Fifth Normal Form .. 51 19 Getting Started with MSAccess 53 A Few Terms .. 53 Screen Layouts .. 54 20 Creating 55 introduction to Tables .. 55 Datasheet Records .. 59 Table Relationships .. 61 Sorting and 62 64 21 introduction to 66 Overview of 66 Algebraic Expressions .. 66 22 Windows Controls and 67 Text-Based 67 23 The Series-Based Functions .. 68 24 The Domain-Based Functions .. 68 25 69 26 Subforms .. 72 27 More Forms .. 73 28 75 29 Importing, Exporting, and 76 30 References: .. 78 Jelena Mam enko introduction to Data Modeling and MSAccess Lecture Notes on Informatikon RESOURCES Part I 5 1 introduction to Data Modeling An important aspect of most every business is record keeping.

8 In our INFORMATION society, this has become an important aspect of business, and much of the world's computing power is dedicated to maintaining and using databases. Databases of all kinds pervade almost every business. All kinds of data, from emails and contact INFORMATION to financial data and records of sales, are stored in some form of a database. The quest is on for meaningful storage of less-structured INFORMATION , such as subject knowledge. This is the first of a two-part article that will provide an introduction to relational databases and the SQL language. This first part describes some of the key elements of the technology with an emphasis on database normalization.

9 The second part will describe a less theoretical approach to database design, as well as provide an introduction to the SQL language. The concept of relational databases was first described by Edgar Frank Codd (almost exclusively referenced as E. F. Codd in technical literature) in the IBM research report RJ599, dated August 19th, However, the article that is usually considered the cornerstone of this technology is "A Relational Model of Data for Large Shared Data Banks," published in Communications of the ACM(Vol. 13, No. 6, June 1970, pp. 377-87). Only the first part of the article is available online. Additional articles by E.

10 F. Codd throughout the 1970s and 80s are still considered gospel for relational database implementations. His famous "Twelve Rules for Relational Databases"2 were published in two Computerworld articles "Is Your DBMS Really Relational?" and "Does Your DBMS Run By the Rules?" on October 14, 1985, and October 21, 1985, respectively. He has since expanded on the 12 rules, and they now number 333, as published in his book "The Relational Model for Database Management, Version 2" (Addison -Wesley, 1990). Codd's twelve rules call for a language that can be used to define, manipulate, and query the data in the database, expressed as a string of characters.