Database Overview Evolution of Database System Evolution of

Database Overview Evolution of Database System

Evolution of Database 1960 s 1970 s 1980 s 1990 s 2000+ File-based Hierarchical Network Relational Object-oriented Entity-Relationship Web-based No. SQL New. SQL Database Design 2

Database: Historical Roots Manual File System To keep track of data Used tagged file folders in a filing cabinet Organized according to expected use • e. g. file per customer Easy to create, but hard to • locate data • aggregate/summarize data Computerized File System To accommodate the data growth and information need Manual file system structures were duplicated in the computer Data Processing (DP) specialists wrote customized programs to • write, delete, update data (i. e. management) • extract and present data in various formats (i. e. report) Database Design 3

File System: Example Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 4

File System: Weakness “Islands of data” in scattered file systems 분산된 파일시스템. Problems Duplication 중복 • Same data may be stored in multiple files Inconsistency 불일치 • Same data may be stored with different values/formats Rigidity 경직성 • Requires customized programming to implement any changes • Cannot do ad-hoc queries 즉석질의 불가 Implications Waste 낭비 of space Data inaccuracies 오류 High overhead 간접비용 of data manipulation and maintenance Database Design 5

File System: Problem Case CUSTOMER file AGENT file A_Name (15 char) A_Name (20 char) Carol Johnson Carol T. Johnson SALES file AGENT (20 char) Carol J. Smith • Inconsistent field name, field size • inconsistent data values • data duplication Database Design 6

Database System vs. File System Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 7

Hierarchical Data Model 계층적 데이터 모델 Hierarchical Model To manage large amount of data for complex manufacturing projects • → Information Management System developed by Rockwell & IBM Files connected in Parent-Child (1: M) relationships • 1 Parent - Multiple Children Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 8

Hierarchical Data Model 계층적 데이터 모델 Strengths u Conceptual Simplicity 개념적 단순성 · · u Centralization of data · Groups of data could be related to each other Related data could be viewed together Reduced redundancy 중복 and promoted consistency 일관성 Weaknesses u Limited representation of data relationships · u Structural Dependence 구조 의존 · u Data access requires physical storage path Complex Implementation 복잡한 구현 · u Did not allow Many-to-Many (M: N) relations Required in-depth knowledge of physical data storage Lack of Standards 표준 부족 · Database Design Limited portability 9

Network Data Model 네트워크 데이터 모델 Network Model → Extension of Hierarchical Model • → Composed of Owner-Member (Parent-Child) sets To represent Many-to-Many (M: N) relationships • Multiple Parents – Multiple Children Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 10

Relational Data Model 관계형 데이터 모델 Problems with legacy database systems Required excessive effort to maintain • Data manipulation (programs) too dependent on physical file structure Hard to manipulate by end-users • No capacity for ad-hoc query (must rely on DB programmers). Relational Model u E. F. Codd’s proposal • Separated the notion of physical representation (machine-view) from logical representation (human-view) → Eliminated pointers and used tables to represent data • Considered ingenious but computationally impractical in 1970 u Dominant database model of today Separation of design from implementation → Flexible · Ad-hoc queries → Structured Query Language (SQL) · Database Design 11

Relational Database: Example Tables (i. e. Relations) u → → Provide a logical “human-level” view of the data and associations among groups of data Organize data into rows 행 (records/tuples) and columns 열 (attributes) Are related via shared attribute(s) Database Design 12

Entity Relationship Model Peter Chen’s Landmark Paper (1976) “The Relationship Model: Toward a Unified View of Data” Graphical representation of entities and their relationships Based on Entity, Attributes & Relationships u Entity → e. g. EMPLOYEE • Thing about which data are to be collected and stored u Attributes → e. g. SSN, last name, first name • Characteristics of the entity u Relationships → i. e. 1: M, M: N, 1: 1 • Associations between entities § Complements the relational data model concepts • • Helps to visualize structure and content of data groups Entity Relationship Diagram (ERD) → Tool for conceptual data modeling → Formalizes a way to describe relationships between groups of data Database Design 13

E-R Diagram: Chen Model Entity 개체 u Relationship 관계 u represented by a rectangle with its name in capital letters. represented by an active or passive verb inside the diamond that connects the related entities. Connectivity 관계유형 u u i. e. , types of relationship written next to each entity box. Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 14

E-R Diagram: Crow’s Foot Model Entity 개체 u Relationship 관계 u represented by a rectangle with its name in capital letters. represented by an active or passive verb that connects the related entities. Connectivity 관계유형 u indicated by symbols next to entities. · · 2 vertical lines for 1 “crow’s foot” for M Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 15

E-R Model: Pros & Cons Advantages u Exceptional conceptual simplicity · · u Integration with the relational database model · Easily viewed and understood representation of database Facilitates database design and management Enables better database design via conceptual modeling Disadvantages u Incomplete model on its own · Limited representational power → → · u cannot model data constraints not tied to entity relationships § e. g. attribute constraints cannot represent relationships between attributes within entities No data manipulation language (e. g. SQL) Loss of information content · Database Design Hard to include attributes in ERD 16

Object-Oriented Database Semantic Data Model (SDM) ► Modeled both data and their relationships in a single structure (object) · Developed by Hammer & Mc. Leod in 1981 Object-oriented concepts became popular in 1990 s ► ► 객체지향 Modularity facilitated program reuse and construction of complex structures Ability to handle complex data types (e. g. multimedia data) Object-Oriented Database Model (OODBM) ► ► Maintains the advantages of the ER model but adds more features Object = entity + relationships (between & within entity) · ► consists of attributes & methods → methods are all relevant operations that can be performed on an object Class Template for objects · · e. g. EMPLOYEE class = (employ 1 object, employ 2 object, …) organized in a class hierarchy → ► e. g. PERSON > EMPLOYEE, CUSTOMER Incorporates the notion of inheritance · Database Design attributes and methods of a class are inherited by its descendent classes 17

OO Database Model vs. E-R Model OODBM: - can accommodate relationships within a object - objects to be used as building blocks for autonomous structures Database Systems: Design, Implementation, & Management: Rob & Coronel Database Design 18

Object-Oriented Database: Pros & Cons Advantages u Semantic representation of data · u u Modularity, reusability, inheritance Ability to handle · · Fuller and more meaningful description of data via object Complex data Sophisticated information requirements Disadvantages u Lack of standards · u Complex navigational data access · u Class hierarchy traversal Steep learning curve · u No standard data access method Difficult to design and implement properly High system overhead · Database Design Slow transactions 19

Web Database Not a database model, but a system u u u For storing information that can be accessed via Web That supports complex data types & relationships In a Client-Server architecture Server hosts database & DBMS (e. g. , My. SQL) · Client accesses the server for database use · Client Initiates a Connection Web Client (e. g. Chrome) Database Design HTTP request Webpage Server Waits & Responds to Incoming Connections Web Server (e. g. Apache) Data request Retrieved data Database DB Server (e. g. My. SQL) 20

No. SQL/New. SQL Database No. SQL (Not Only SQL) u u Non-relational: e. g. , objects instead tables For big (unstructured, distributed) data & real-time Web applications More scalable & better performance Flexible & agile development New. SQL u No. SQL + Relational Consistent → Scalable → Flexible → Database Design 21

MS Access Introduction Database Design

MS Access: Overview MS Access A Database Management System (DBMS) • designed to create applications that organize, store, retrieve, and manipulate large collections of data. GUI-driven with built-in automations Based on relational database theory Theory & Practice Need a solid understanding of database theory and principles as well as DBMS skills to develop an effective database system. DBMS expert ≠ Database Design expert

MS Access: Database Objects • Objects – Tables store data – Forms display data • for viewing, editing, entering – Reports summarize & present data • forms can change data, but reports cannot. – Queries manipulate data • combine, filter, modify, retrieve, etc. • – Macros are simplified programs that automate tasks. – Modules are Visual Basic for Application (VBA) programs. Object Views – (Normal) View • the mode used to interact w/ the object & its data. – Design view • the mode used to change the object design. GCF Learn. Free. org

MS Access: Database Design User Interface GCF Learn. Free. org 25

MS Access: The Ribbon Minimize Ribbon GCF Learn. Free. org Ø Common activities/commands organized by tabs & groups • Tabs → Groups → Commands Database Design 26

MS Access: Quick Access Toolbar Right Click Ø Direct access to any command • • Database Design Click Customize Quick Access Toolbar icon & select More Commands… Right-click a command & click Add to Quick Access Toolbar 27

MS Access: Navigation Pane Ø Contains every object in the database Design • Objects are organized into groups by type • Double-click the object name to open • Right-click to rename • Press the delete key to delete objects • Click the double arrow to minimize/maximize the navigation pane 28

MS Access: File Tab Ø Shows general file commands Database Design • Recent → Recently open files • Options → Access options 29