Introduction to Information Systems and Database Design

Course Overview and Introduction to Databases

• Course Introduction:

  • The instructor will provide voice-over slides to the team group.

  • Early lectures on database design and modeling utilize whiteboard handwriting alongside slides.

  • The later, programming-heavy portion of the course focuses on SQL (Structured Query Language) and uses the Oracle Database Management System (DBMS).

  • The course consists of approximately $15$ to $16$ lectures in total.

• Course Outline:

  1. Database Concepts: Establishing a shared understanding of fundamental definitions.

  2. Relational Databases: Exploring the most popular category of database systems.

  3. Database Design and Conceptual Modeling: Learning the architecture of data structures.

  4. Case Studies: Real-world applications of design principles.

  5. SQL Programming: Hands-on development using Oracle (students may choose other software, but Oracle is the lecturer's choice).

• Assessment Structure:

  • Midterm Exam: $30\%$ of the final grade.

  • Assignment: $20\%$ of the final grade.

  • Final Exam: $50\%$ of the final grade.

  • Testing Conditions: Due to current circumstances, exams are conducted online.

    • The exams are open-book, and access to the Internet is permitted.

    • Warning regarding Internet use: The instructor advises against relying on Internet searches during the exam because online information can be inaccurate. Students should prioritize their lecture notes and the instructor's specific methodologies.

    • Communication Policy: Students are trusted not to communicate with others during the test. Disagreements with course content should be discussed in the lecture, not raised during the assessment.

Recommended References and Faculty

• Core Reading Material:

  • C.J. Date (C.J.D.): An Introduction to Database Systems. This was the first standard textbook in the field and reached its $8^{th}$ edition before the author stopped updating it. Diagrams used in the first part of the course originate here.

  • Elmasri and Navathe: Fundamentals of Database Systems. Used for diagrams in the second part of the course. Any edition is acceptable.

  • Ullman, Widom, and Garcia-Molina: Database Systems: The Complete Book.

    • Jeffrey Ullman: Recipient of the Turing Award (the "Nobel Prize of Computing").

    • Jennifer Widom: Dean of Engineering at Stanford University.

    • Hector Garcia-Molina: Known as one of the world's most highly-cited researchers; he recently passed away.

• Faculty Mentions:

  • Ajahn Visid (Visid Hiranprakit): A top student who did his senior project with the lecturer and became a faculty member.

  • Ajahn Natapong: A student of Ajahn Visid and a fellow lecturer.

  • Both are noted experts in Logic and Artificial Intelligence.

Fundamental Database Components

• Architecture Hierarchy:

  • Database: Located in the center; consists of the actual data and metadata (information about the data).

  • Database Management System (DBMS): The software layer that manages the data.

  • Application Programs and End Users: Interact with the DBMS to access the database.

• The SQL Standard:

  • SQL is the standard language for relational databases.

  • It is currently recognized as the most popular programming language globally according to recent surveys.

  • In diagrams, dotted lines represent SQL statements sent to the DBMS, which processes them and returns results to the application or user.

• Course Scope vs. Future Study:

  • This Course: Focuses on database concept design and SQL programming.

  • Next Course (Database Systems): Focuses on DBMS internals, including multi-user concurrency, transaction processing, and internal architecture differences (e.g., Oracle vs. Microsoft SQL Server).

Defining the Database: Objects and Relationships

• Core Definition: From an Information Systems perspective, a database is a collection of data objects and the relationships among them.

  • Alternative definitions (e.g., a simple collection of image files) exist in other fields like image processing, but this course focuses on Enterprise Databases (e.g., for banks, telecommunications, or large industrial companies).

• The Relational (Tabular) Format:

  • Represented by tables where rows represent objects.

  • Example Model:

    1. Supplier Table ($S$): Attributes include Supplier Number ($S1$), Name (Smith), City (London), and Status ($10$). Each row is an object.

    2. Part Table ($P$): Attributes include Part Number ($P1$), Name (Bolt), Color (Red), Weight ($12$), and City (London).

    3. Shipment Table ($SP$): This table represents a relationship. A row might show $S1$ (Supplier) and $P1$ (Part) with a Quantity of $300$. It connects the two object tables.

• The Hierarchical (Tree) Format:

  • Data is structured as a tree. Nodes represent objects, and branches represent relationships.

  • Example: A root node for a Supplier ($S1$, Smith, London) has children nodes for each part they supply ($P1$, $P2$…$P6$), including specific quantities.

Database Models and Performance Debates

• Components of a Data Model:

  1. Representation: The format (e.g., Table vs. Tree).

  2. Language: The tool used to query the data (e.g., SQL for tables vs. Tree-based languages like DL/1).

  3. Rules/Regulations: Constraints on the data (e.g., in a tree model, a node can have only one parent).

• Historical Performance Conflict:

  • Thirty to forty years ago, IBM dominated the market ($90\%$ share).

  • IMS (Information Management System): An IBM software supporting the Hierarchical Model.

  • DB2: An IBM software supporting the Relational Model.

  • The Speed Misconception: Early benchmarks showed IMS was faster than DB2 on the same hardware. Many concluded the Hierarchical model was inherently superior for speed because it used direct pointers from parents to children.

• The Logical vs. Physical Reality:

  • Database models are logical representations, not descriptions of physical storage.

  • IMS was faster simply because it was $10$ years older; it had fewer features and was optimized for older, more restrictive hardware. When run on modern hardware, it appeared fast but lacked modern functionality.

  • Comparison Analogy: Comparing Microsoft DOS (small, fast, minimal features) to Microsoft Windows (resource-heavy, feature-rich, high productivity).

  • Conclusion: Data models should be compared based on Productivity and Suitability, not speed.

Contemporary Design Debate: Relational vs. Document

• The University Registration Example:

  • Currently, universities like KMITL use relational tables: Student, Course, and Enrollment.

  • To produce a transcript, the system must perform a "join" operation on these three tables.

• The Document/Hierarchical Alternative:

  • Some argue for keeping data in "Transcript Format" (Document Database).

  • In this model, when a student arrives, a blank transcript document is created. Grades are simply filled in over time.

  • Benefit: Retrieving a transcript requires minimal processing since the document is already formed.

  • Critique: Critics ask if this complicates data entry for multiple professors or office staff.

• Discussion Prompt: Why do we continue to use relational tables instead of storing data in a finished document format? (Student was asked to consider this during a break).

Questions & Discussion

• Student Question: Regarding performance, which is better: Relational or Hierarchical? Maybe Relational is better for multithreading and hashing?

• Professor Answer: People in the past preferred Hierarchical because of pointers. However, you cannot compare logical models by speed. The choice depends on features and how easily you can accomplish tasks (productivity). Historically, IBM forced the shift to Relational by announcing they would prioritize DB2 over IMS, regardless of the speed differences users observed.