What is Database? Get Help In Database Assignment

  • Database, a data collection stored as an organized set of files on a computer

  • Database Transaction, a sequence of operations to read (query) data from or write (insert, modify) data into the database

  • Database Management System (DBMS) – a software system that supports creation and population of the databases as well as database transactions


Data Redundancy & Inconsistency

  • Multiple file formats, duplicate info in different files

Difficulty in accessing data

  • Need to write a new program to carry out each new task

Data isolation — the files are not connected with each other

  • Integrity problems

  • Integrity constraints (e.g., GPA >= 0) become “buried” in program code rather than being stated explicitly

  • Hard to add new constraints or change existing ones


What is a Relational Database?

Relational Databaseis a database that stores data as a set of Tables (Relations) with:


Set of Columns

  • One column is called a Primary Key

  • All values in a Column have the same data type


Set of Rows

  • Rows have entries that correspond to Columns

  • Each Row is Unique based on the Primary Key

  • Primary Key must have a unique value (not NULL or empty)

  • Row Entries not in the Primary Key do not have to be unique

  • Non-Primary Keys entries for a Row can be NULL or empty

  • Rows of data in their raw form are stored in Tuples

Relational Database Management Systems (RDBMS), a set of programs for managing and facilitating all the operations on a database while maintaining the integrity and security of the data, e.g. Oracle RDBMS, IBM DB2, PostgreSQL












Data Types for Relational Databases

  • char(n) Fixed length character string, with user-specified length n

  • varchar(n) Variable length character strings, with user-specified maximum length n.

  • int. Integer (a finite subset of the integers that is machine-dependent).

  • smallint. Small integer (a machine-dependent subset of the integer domain type).

  • numeric(p,d). Fixed point number, with user-specified precision of p digits, with n digits to the right of decimal point.

  • real, double precision. Floating point and double-precision floating point numbers, with machine-dependent precision.

  • float(n). Floating point number, with user-specified precision of at least n digits.

  • Most systems also have other types such as Date, Time, URL, etc.


Relations :: Logical View

Relation (Table) is a set of Tuples

  • Recall a set cannot have duplicates and the order of elements is not significant


Tuple (Row) is a sequence of elements

  • Tuples can have duplicates

  • Order of elements in a tuple is significant

  • <> = denotes the structure of a Tuple

Examples:

•A Tuple:

<1, “Jane”, 25, “female”>


•A set of Tuples:

{ <1, “Jane”, 25, “female”>,

<2, “John”, 18, “male”> }


Note that the values that occur in the corresponding positions in the two tuples are of the same type



RDBMS Characteristics

  • Schemas & Relational Model

  • Concurrency

  • ACID Transactions

  • “Join” operations


Table Schema(s)

  • Structure of the DB tables

Relational Database Model

  • Mapping of the DB table connections

Concurrency

  • Multiple concurrent transactions from multiple users at the same time without any unintended and undesirable effects

Atomic Data Structures

  • Only single data types, e.g. Integers, Characters, Boolean, String

  • No lists, arrays, dictionaries, JSON objects or XML documents


ACID Transactions

  • Atomicity: Every transaction is either entered into the DB in its entirety or not at all

  • Consistency: Only those transactions are executed which will not make a consistent DB inconsistent

  • Isolation: Each transaction is executed as if it is the only transaction being executed at that time

  • Durability: Once a transaction is committed, it remains in the DB even in the face of system failures

Relational Database Model

How are these tables connected?












Relational Database Model















From a High Level, shows how the Database Tables are connected to one another to form a Database.


Database Table Schemas

  • Note: Database Table Schemas rarely change. The Data in the Database Tables can change frequently.

  • The Structure is often referred to as the ‘Schema’ of a table in textbooks.

1) A Table is its Schema plus + data stored in it (data with Metadata)


Let’s Drill down into the Relational DB Model












Database Schema Example

Friends(name of the schema, not part of the schema) – Table Name

  • Name: Varchar (String)

  • Age: Integer

  • Gender: Varchar (String)

Also represented as:

  • Friends(Name: Varchar (String), Age: Integer, Gender: Varchar (String))

Or as:

  • Friends(Name, Age Gender)

2 views0 comments