Lecture 0: Introduction to Database

Question 1

What is a Metadata

Answer 1

metadata are data that provides information about other data. They can include relationships, permissions and the structure of the tables

Question 2

What is a DBMS?

Answer 2

Database Management System. This is a software that allows users to control the database through either:

1. Creating
2. Modifying
3. Maintaining etc..

Question 3

What is a database application program?

Answer 3

It is a software application that interacts with a DB by using the correct commands . E.g. MySQL workbench, Command Line Interface, any 3rd parties

Question 4

What is a database server?

Answer 4

it is a dedicated computer system which runs the DBMS and contains the database and underlying data? This may or may not be a distributed system.

Question 5

DDL?

Answer 5

Data definition language. Specifies data types and structures. Specifies the database schema

Question 6

DML?

Answer 6

data manipulation language. allows insertion, deletions, retrieval. used to both read and update the database

Question 7

SQL?

Answer 7

structured query language. it is the combination of ddl and dml. it is a high level programming language with rules on what to say in the language, and ensuring you understand how the computer will interpret what you say

Question 8

What are the steps on building an entity relationship Model (the conceptual design) ?

Answer 8

1. Identify the entity types
2. Identify the relationship types:
   - name
   - direction
   - multiplicity
3. Identify the attributes
4. State the Primary Keys

Question 9

What is a logical DB design?

Answer 9

A logical design, is when the entity relationship model turns into the relation model (a table that defines how data is related). Also includes the foreign keys

Question 10

What happens to the DB from logical to Physical design?

Answer 10

The logical design (relational model) gets translated into a physical implementation.

The physical implementation allows data to be stored and queried

Physical implementation is created using SQL

Question 11

Describe what a database schema is.

Answer 11

The overall description of a databases structure.

Question 12

How many types of schemas are there?

Answer 12

3. The DBMS is responsible for mapping between these schemas

Question 13

How many types of data independence are there?

Answer 13

2. Logical and Physical
   Logical is the immunity between external and conceptual.

Physical refers to the immunity of the conceptual and internal

Question 14

what is a data model?

Answer 14

Is a collection of concepts that can be used to describe a set of data
the operations to manipulate the data
and a set of integrity constraints for the data

Question 15

How many data models are there?

Answer 15

3.
conceptual data model
logical data model
physical data model

Question 16

What is another name of conceptual data model?

Answer 16

object based data model.

Conceptual data models use objects such as entities, attributes and relationships to describe a use case

Question 17

What is a logical data model?

Answer 17

represents a database as a number of fixed-format record. Is constructed based on the conceptual model.

Question 18

What is another name for logical data model?

Answer 18

record based models

Question 19

Give examples of a logical data model

Answer 19

relational
network
hierarchical

Question 20

What is the client-server architecture?

Answer 20

there is a client that requires a resource and a server that provides that resource. in this approach the server handles the database functionality

Question 21

Explain three-tier model (n-tiers).

Answer 21

user interface (presentation), software functionality (business logic) and database, are distributed over different machines that together make a usable solution

Question 22

What is an index?

Answer 22

indexes allow the database application to find data fast, without reading the whole table.

Question 23

What are the disadvantages of using indexes

Answer 23

They take take up disk space and have to be updated each time the data is modified.

Question 24

What is a view?

Answer 24

a view is a virtual table based on the result-set of an SQL query.

Usually you can save a query in the database, so this is a good way to do it if you frequently run the same query

Question 25

What does a view contain

Answer 25

contains row and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE and JOIN statements to a view and present the data as if the data were coming from one single table.

Question 26

Where do we perform views?

Answer 26

We add views for queries that would need to be performed often.

E.g. Salon database daily appointments might need to be printed daily, so we would create the following view

Question 27

What two types of database security does relational DBMS provide?

Answer 27

1. system Security

2. data security

Question 28

explain system security

Answer 28

covers access and use of the database at the system level. such as user name and password

Question 29

explain data security

Answer 29

covers access and use of database objects (such as relations and views) and the actions that users can have on the objects

Question 30

How do you configure these security settings?

Answer 30

through mySQL workbench.

Question 31

how do you export data ?

Answer 31

mysqldump -u root -p DATABASE NAME > FILE LOCATION\DATABASE NAME.sql