MIS_Week_22

Management Information Systems - Week 2

Instructor Information

• Instructor: Yuyang Ye

• Email: yuyang.ye@rutgers.edu

• Date: January 29, 2025

Database Models and Their Components

• Definition: A database model is a conceptual framework for organizing and managing data.

Components of Database Models

• Data Structures: Prescribe how data is organized.

• Operations: Manipulate data structures.

• Rules: Govern valid data use.

Types of Database Models

1. Relational Model:

   • Strengths:

     • Fast queries and efficient storage.

     • Productivity and simplicity in transactional applications.

     • Integration with object-oriented programming languages.

     • Flexible schema accommodating business requirements.

     • Support for unstructured and semi-structured data.

2. Other Models:

   • Hierarchical

   • Network

   • Object

   • Graph

   • Document (e.g. JSON, XML)

Example Database Systems

• Examples:

  • IMS, IDMS, Oracle Database, ObjectStore, Neo4j, MongoDB

• Primary Data Structures:

  • Tree, Linked list, Table, Class, Vertex and edge

Relational Database Overview

• Introduced by: E. F. Codd of IBM in 1970

• Standardization:

  • Operations and rules standardized in SQL, the universal query language for relational databases.

Key Aspects of the Relational Model

• Tables (relations) store data.

• Rows (tuples) represent records.

• Columns represent attributes.

Set Theory in Relational Model

Concepts

• Set: Unordered collection of elements (e.g. {a, b, c}).

• Tuple: Ordered collection enclosed in parentheses (e.g. (a, b, c)).

Relational Data Structures

Tables

• A table:

  • Has a unique name.

  • Contains a fixed tuple of columns with varying sets of rows.

Columns and Rows

• Column:

  • Has a name and a data type.

• Row:

  • Unnamed tuple of values corresponding to columns.

  • Types of values determined by column's data type.

Relational Operations

Basic Operations

• Select: Chooses a subset of rows.

• Project: Chooses specific columns.

• Product: Lists all combinations of rows from two tables.

Advanced Operations

• Join: Combines tables based on related columns.

• Union: Selects all rows from two tables.

• Intersect: Selects common rows between two tables.

Final Operations

• Difference: Selects rows unique to one table.

• Rename: Changes a table's name.

• Aggregate: Functions over multiple rows (e.g. sum, count).

Relational Rules

Key Constraints

1. Unique Primary Key:

   • All tables must include a unique primary key.

2. Unique Column Names:

   • No duplicate names within the same table.

3. No Duplicate Rows:

   • No identical rows across all columns.

Business Rules

• Definition:

  • Based on specific business policies.

• Examples:

  • Employee records must include valid department codes.

  • Passport Numbers must be unique.

Structured Query Language (SQL)

Overview

• SQL: Standard query language for relational databases.

SQL Sublanguages

1. Data Definition Language (DDL):

   • Defines database structure (CREATE, ALTER, DROP).

2. Data Manipulation Language (DML):

   • Manages data within schema objects (SELECT, INSERT, UPDATE, DELETE).

3. Data Query Language (DQL):

   • Queries data (e.g. SELECT).

4. Data Control Language (DCL):

   • Controls data access (e.g. GRANT, REVOKE).

SQL Language Elements

Statement Structure

• Definition: Complete, executable instructions ending with a semicolon.

Key Elements

• Literal: Explicit value (e.g. 'Maria', 129).

• Keyword: Special meaning in SQL (e.g. SELECT, INSERT).

• Identifier: Database object names (e.g. FirstName, Population).

• Expression: Sequence evaluating to a value (e.g. Population > 1000).

• Comment: Ignored by the processor.

Tables, Columns, and Rows

Table Characteristics

• Table:

  • Must have a unique name and at least one column.

• Column:

  • Each column must have a unique name and data type.

• Row:

  • Each row is an unnamed sequence of values corresponding to column data types.

Primary and Foreign Keys

Primary Keys

• Definition: Identifies each row uniquely in a table.

• Types:

  1. Simple Primary Key: Single field.

  2. Composite Primary Key: Multiple fields.

  3. Candidate key: Possible choices for primary keys.

Foreign Keys

• Definition: Attribute matching primary key from another table.

• Example: In CITY and STATE tables, StateAbbrev serves as a foreign key.

Entity-Relationship Diagrams (ERDs)

Overview

• Visual representation of relationships between database tables.

• Entities correspond to tables; relationships shown by connecting lines.

Creating ER Diagrams

1. Open diagrams.net: Start a new diagram.

2. Add Entities: Use rectangles for entities and label them.

3. Define Attributes: Use ovals for attributes connected to entities.

4. Specify Relationships: Use diamonds for relationships, labeling connections.

5. Add Cardinality: Indicate relationship types (e.g. 1:M).

Database Design Practice

Case Study: Plumbing Store

• Entities: Customer, Orders, Product, Ordersdetail.

• Relationships:

  • Customer has many Orders (1:M).

  • Orders has many Orderdetails (1:M).

  • Product can appear in many Orderdetails (1:M).

Database Design Problems and Solutions

Database Design Challenges

• Common design issues include redundancy and poor normalization affecting data integrity.

Normalization Process

• Reduce redundancy by restructuring tables ensuring dependencies link to primary keys only.

• Types of anomalies resolved through normalization:

  1. Insertion Anomaly: Inability to add data due to missing information.

  2. Deletion Anomaly: Unintended data loss via record deletion.

  3. Update Anomaly: Multiple updates required for single information change.

Homework Assignments

• Readings: Cover Chapter 4, SQL Workbench Tutorial sections.

• Practice: Create ERDs using diagrams.net.

Additional Reading

• CREATE TABLE & DROP TABLE statements.

• Example of ALTER TABLE in MySQL.

• Familiarize with MySQL data types and characteristics.

Key Data Types Examples

• Integer: INT, SMALLINT, etc.

• Decimal: FLOAT, DECIMAL(M,D).

• Character: VARCHAR, CHAR.

• Date and Time: DATETIME, DATE.