Question

* when to use a database instead of a flat file

* What the primary characteristics of a structured database

Use Rows (records)and Columns (Fields) to store records

* What is the difference between non-structured and semi-structured data

* What is the key structures in a relational database

* what is the differences between a primary key and a foreign key

a table in a database must have 1 primary key and can have several foreign keys

* Know the four commands used to manipulate data in a table and what they do

SELECT, INSERT, DELETE, and UPDATE

* Understand the commands for data definition

CREATE, ALTER, and DROP

Answer 1

When to use Database instead of Flat File:

Flat files store data in plain text format and does not support DML and DDL commands whereas Database can be used to store large amount of data and can be manipulated and defined as per our need. So whenever we want data consistency, data Isolation, data integrity less redundancy , more security , it is better to use database instead of flat files. As database has good features as compared to Flat file when we want to handle alot of data efficiently.

Primary Characteristics of Structured Database:

Structured database stores the data in a tabular format with rows (records) and columns(Fields). Every record is uniquely identified with the help of a primary key.There are several other keys such as foreign key,candiate key,composite key ,super key,etc.

• Data is organized in a proper manner(i.e in Tables)
• Data is stored in field within a record or a file
• There are different entities and each entity has several attributes.
• Enitities can together form relations .
• Data can be easily accessed using query language(SQL).

Difference between Non- Structured and Semi - Structured data:

Non-structured data are unorganized data and does not have a predefined model whereas Semi-Structured data has some organizational properties even though it does not reside on relational database.Non-structured data are more flexible than semi-structured data.Example of Non-structured data is Text,Media logs and example of semi-structured data is XML data.

Key structure in Relational Database:

Key are used to identify the reords in a table or to obtain relationship between tables. There are several keys used in relational database.

1.Super Key:Group of single or multiple keys that identifies rows in a table.

2.Primary Key:Uniquely idetifies a record in a table.

3.Candidate key:Set of attributes that uniquely identifies tuples in table

4.Alternate key:Column or group of columns that uniquely identify every row in the table.

5.Foreign key:Creates link between two tables(data intergrity)

6.Composite key:Combination of 2 or more columns that uniquely identifies rows in table.

7.Surrogate key: Aritificial key which uniquely identifies each record in the table

Difference between Primary Key and Foreign Key:

Primary key  uniquely identifies each record in the table whereas Foreign key is used to create a link between two tables in which this key refers to the primary key in the other table. Primary keys cannot have duplicate values but foreign key can have duplicate values. Primary key cannot have null values but Foreign key can have null values.

Data Manipulation Commands:

These commands are used to manipulate the data which is stored in the databse .

1.SELECT: It is used to display the whole table or the specific columns of the table as per the condition.

Syntax: SELECT * FROM table_name;

(This is used to display the whole table)

Eg: SELECT * FROM STUDENT;

(This will display all the columns and values which are there in Student table)

Syntax : SELECT column name FROM tablename WHERE condition ;

(Here specific column from specific table is displayed as per condition)

Eg:SELECT Student_Name FROM STUDENT WHERE Rollno <15.

(This will display the name of students whose roll no is less than 15)

2.INSERT: This command is used to insert values into the table.

Syntax:INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

OR

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

(Both the syntax can be used to insert values into a table)

Eg:INSERT INTO STUDENT (STUDENT_NAME, ROllno)
VALUES (ALBERT, 1);

OR

INSERT INTO STUDENT
VALUES (ALBERT, 1);

3.DELETE:This command is used to delete a specific record from the table.

Syntax: DELETE FROM table_name WHERE condition;

Eg:DELETE FROM STUDENT WHERE Rollno=15;

(Here the record at roll no 15 will be deleted from the table)

4.UPDATE: This command is used to change the values in the table as per condition.

Syntax:UPDATE table_name SET column1 = value1, column2 = value2, ...WHERE condition;

Eg:UPDATE STUDENT SET STUDENT_NAME = ALAN WHERE ROllno =17;

(The student_name will be change to ALAN where the roll no is 17)

Data definition commands:

These commands are used to create the structure of the databse such as creating a table in a database or making changes or removing something from table or database.

1.CREATE: This command is used to create a database as well as to create a table in the database

Syntax: CREATE DATABASE database_name;

(This is used to create a database )

Eg: CREATE DATABASE SCHOOL;   

(Here school is the name given to the database.)

Syntax: CREATE TABLE tablename ( Column1 (datatype), Column2(datatype),........);

(This is used to create a table)

Eg: CREATE TABLE STUDENT( Student_Name VARCHAR(20) , Rollno INT);

(Here tablename is STUDENT and the columns are Student_Name and Roll number ,with their datatypes varchar and int respectively)

2. ALTER: This command is used to alter any changes in the table which are created in database like  adding new column , modifying existing columns .

Syntax: ALTER TABLE table_name ADD column_name datatype;

(This is used to add a new column in the table)

Eg: ALTER TABLE STUDENT ADD Date of birth  YEAR;

(Here Column Age is added to the table STUDENT)

Syntax: ALTER TABLE table_name RENAME to New_table_name;

(This is used to change the name of the table)

Eg: ALTER TABLE STUDENT RENAME to  STUDENTS;

(The table is renamed as STUDENTS)

Syntax:ALTER TABLE table_name MODIFY column_name datatype;

(This is used to modify the datatype of a column)

Eg: ALTER TABLE STUDENT MODIFY  Date of birth DATE;

(Now the datatype is modified to date i.e it will show the whole date instead of only year)

Syntax: ALTER TABLE table_name DROP column_name ;

(This is used to drop a column from the table)

Eg: ALTER TABLE STUDENT  DROP Date of birth;

(This will drop the column Date of birth from the STUDENT table)

3.DROP: This command is used to drop the database or a table from the database i.e to remove complete the database or table from the database

Syntax: DROP DATABASE database_name;

(This is will remove the whole database )

Eg:DROP DATABASE SCHOOL;

(here the Student database is removed)

Syntax: DROP TABLE table_name;

(This will remove the table from the database)

Eg:DROP TABLE STUDENT;

(here the Student table is removed from the database)