MY SQL

MY SQL

Database

• Organized collection of data

Field

Set of characters that are used together to represent specific data elements/ data item. Individual data item within a record is known as a field

RECORD

Collection of fields is termed as record

RELATION

A collection of logically related records is called file/ table/ relation.

Database Management System (DBMS)

Software that allows users to define create, access and manage database(s) is termed as Database Eg., MS Access, MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Oracle, SAP, dBASE, Foxpro etc.

Database Management System

• Stores data in structured way

• Query the database

• Sort and manipulate the data in database

• Validate the data entered and check for inconsistencies

• Produce flexible reports, both on paper and screen

Advantages of DBMS

• Control Data Redundancy

• Data Consistency

• Sharing of Data

• Reduced Programming Effort

• Database Enforces Standards

• Improved Data Integrity

• Privacy and Security

Relational Data Model

Data organized in two dimensional tables called relations

RELATIONAL DATABASE

A relational database is a collection of data and items organized as a set of tables from which data can be retrieved easily and efficiently.

Attributes

Columns in a database

TUPLE

Each row in a table is known as tuple

Cardinatlity of Relation

Number of records or tuples

Degree of Relation

Number of columns or attributes

Domain of Relation

Set of all possible values that an attribute may contain

Primary Key

An attribute or a group of attributes that can uniquely identify tuples within the relation.

CANDIDATE KEY

A candidate key is one that is capable of becoming the primary key

ALTERNATE KEY

Candidate key that is not a primary key

FOREIGN KEY

A non-key attribute whose value(s) are derived from the primary key of some other table is known as foreign key

Difference between SQL and MySQL

SQL is a standard language for accessing and manipulating databases. SQL commands are used to create, transform and retrieve information from RDBMS.

MySQL

is an open source and freely available RDBMS that uses SQL.

Types of commands

DDL (Data Definition Language) – Create, use, alter, drop

DML (Data Manipulation Language) – Select, insert, update, delete

DCL (Data Control Language)

TCL (Transaction Control Language)

Multiple Choice

DML expands to

• Data Management Language

• Data Manipulation Language

• Data Manipulative Language

Multiple Choice

In Relational Database Model, a table is also referred to as a

• tuple

• attribute

• relation

• degree

Multiple Choice

The total number of tuples in a relation is referred to as

• Cardinality of the relation

• Degree of the relation

• Cartesian product

• None of the above

Multiple Choice

Set of values from which an attribute takes values from.

• degree

• cardinality

• domain

• table

Multiple Choice

Which of the following statements are invalid?

• Statement 1 : A relation can have multiple candidate keys

• Statement 2 : A relation can have multiple primary keys

• Statement 3 : A relation can have multiple columns with Unique constraint

Multiple Choice

Degree of a relation refers to the total number of

• rows

• columns

• tables

• databases

Multiple Choice

Aditi wants to apply a constraint on a table “student” , where in she wants to restrict the marks entered as values between only 1 and 100. Which of the following constraints should she use?

• Default

• Check

• Primary Key

• Unique

Multiple Choice

Which of the following are DDL commands?

• Create table , Insert into , Drop table

• Alter table, Update , Drop table

• Drop table, Alter table, Create table

• None of the above

Multiple Choice

An attribute in a relation is a foreign key if the _______ key from one relation Is used as an attribute in that relation.

• Candidate

• Primary

• Alternate

• None of the above

Multiple Choice

A _________ integrity constraint requires that the values appearing in specified attributes of any tuple in the referencing relation also appear in specified attributes of at least one tuple in the referenced relation.

• Referential

• Referencing

• Primary

• None of the above

Multiple Choice

The primary key must be

• Unique

• Not null

• Both Unique and Not nul

• Either Unique or Not null

Multiple Choice

What is a candidate key in MySQL?

• Used to identify a column

• Alias for foreign key

• Alias for primary key

• Used to uniquely identify a row

Multiple Choice

A table can have

• One primary key and more than one candidate key

• One candidate key and one primary key

• More than one primary, alternate keys

• One candidate key only.

Multiple Choice

Relationship between Primary key (P), Alternate Keys(A), and Candidate keys(C) are:

• P=A+C

• C=P+A

• A=P-C

• A=P+C