DBMS

Database - An organised collection of inter-related data that ensures safety , security and integrity of data.

DBMS - software that provides essential services to create, maintain and manage the database.

Advantages:

• Control of data redundancy

• Data consistency

• Sharing of data

• Database ensures standards ( predefined rules and standards have to be followed)

• Privacy and security

• Backup and recovery

Relation Database - Data organised as tables

Terminology

• Entity

• Attribute - columns

• Tuple - row

• Relation - table

• Domain - pool of data from which original data is collected

• Cardinality - no of rows

• Degree - no of columns

Relational Database Management system - software which provides essential tools to create, manage and maintain data in the form of tables

Keys:

Keys are attributes that allow us to identify an attribute or set of aatributes.

Different types of keys:

• Primary Key - an attribute or a set of attribute that allows us to uniquely identify a tuple or row in a table. They are unique , cant be duplicated.

• Candidate Key - attributes which can be selected as the primary key

• Alternate Key - candidate key which is not selected as primary key

• Foreign Key - attribute of a table that is the primary key of another table

Parent table - primary key

Child table - Foreign key

SQL (Stuctured query language):

• lanuage - used to access and manipulate databases

• create , retrive info

MySQL

Data types - numeric and non numeric

NUMERIC

1. Interger: positve 11 and negative 10

2. Numeric [Number(x,y)]: x - how many , y - decimal , 1 for decimal point

3. Decimal - same

NON-NUMERIC

1. char(x): fixed length

2. varchar(x): variable length

3. Date: ‘yyyy-mm-dd’

4. Datetime: ‘yyyy-mm-dd hh:mm:ss’

SQL commands:

1. DDL - definition - create, delete, change the structure - create, drop, alter , crete database, use database

2. DML - manipulation - insert, update, delete data within the database.

3. DCL - control - grant permissions and control access to the database, including commands such as GRANT and REVOKE.

4. TCL - transaction control - manage transactions in a database, ensuring data integrity through commands like COMMIT, ROLLBACK, and SAVEPOINT.

5. DQL - query - select (extract data)