Lecture 0: Introduction to Database Flashcards
What is a Metadata
metadata are data that provides information about other data. They can include relationships, permissions and the structure of the tables
What is a DBMS?
Database Management System. This is a software that allows users to control the database through either:
- Creating
- Modifying
- Maintaining etc..
What is a database application program?
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
What is a database server?
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.
DDL?
Data definition language. Specifies data types and structures. Specifies the database schema
DML?
data manipulation language. allows insertion, deletions, retrieval. used to both read and update the database
SQL?
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
What are the steps on building an entity relationship Model (the conceptual design) ?
- Identify the entity types
- Identify the relationship types:
- name
- direction
- multiplicity - Identify the attributes
- State the Primary Keys
What is a logical DB design?
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
What happens to the DB from logical to Physical design?
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
Describe what a database schema is.
The overall description of a databases structure.
How many types of schemas are there?
- The DBMS is responsible for mapping between these schemas
How many types of data independence are there?
- Logical and Physical
Logical is the immunity between external and conceptual.
Physical refers to the immunity of the conceptual and internal
what is a data model?
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
How many data models are there?
3.
conceptual data model
logical data model
physical data model
What is another name of conceptual data model?
object based data model.
Conceptual data models use objects such as entities, attributes and relationships to describe a use case
What is a logical data model?
represents a database as a number of fixed-format record. Is constructed based on the conceptual model.
What is another name for logical data model?
record based models
Give examples of a logical data model
relational
network
hierarchical
What is the client-server architecture?
there is a client that requires a resource and a server that provides that resource. in this approach the server handles the database functionality
Explain three-tier model (n-tiers).
user interface (presentation), software functionality (business logic) and database, are distributed over different machines that together make a usable solution
What is an index?
indexes allow the database application to find data fast, without reading the whole table.
What are the disadvantages of using indexes
They take take up disk space and have to be updated each time the data is modified.
What is a view?
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
What does a view contain
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.
Where do we perform views?
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
What two types of database security does relational DBMS provide?
- system Security
2. data security
explain system security
covers access and use of the database at the system level. such as user name and password
explain data security
covers access and use of database objects (such as relations and views) and the actions that users can have on the objects
How do you configure these security settings?
through mySQL workbench.
how do you export data ?
mysqldump -u root -p DATABASE NAME > FILE LOCATION\DATABASE NAME.sql
