-
Give me the 3 classifications of a database system.
- Number of users - single / multi user
- Location - centralised / decentralised
- Type of use - transactional / datawarehouse
-
A DBMS is composed of 5 major parts. List them.
• Hardware
• Software
• People
• Procedures
• Data
-
What is the DDL and who might use it?
This is used by the DBA to specify the conceptual schema.
-
Describe the 3 levels of data abstraction (3 schema architecture).
Physical - this is the physical layer on the disk.
Conceptual - this describes the structure of the whole database for a community of users.
View - includes a number of external schemas or user views.
-
Explain what SDL is and provide real life examples of how it works in your answer.
SDL is the Storage Definition Language.
The storage structures and access methods used by the database system are specified by the SDL.
This sits at the internal or physical level.
-
List the pros & cons of each historical data system that was developed over time.
- 1960s
- Hiearchical - data organised into a tree (Windows registry)
- Network - data organised into a tree with each record having one parent and many children.
- 1970s
- Relational databases introduced.
- 1980
- RDMS products started to emerge.
- 1980s - 90s
- OODBMS - Object Oriented DBMS - used in complex data processing in CAD and other apps. Not popular.
-
Give me a command that will insert a new row into the employee table.
The details are
TableName: Employee
Name: Michael
DOB: 01-01-80
SSN: 2265989
Address: 16 Boroimhe Oaks, Swords.
HairColour: Don't know
Height: 5ft10
Assume the rows are in that order.
- INSERT INTO employee
- Values
- ('Michael','01-01-80','2265989','16 Boroimhe Oaks, Swords',NULL,'5ft10')
-
When do you use HAVING instead of WHERE?
WHERE filters data before it is grouped. Data does not need to be grouped for WHERE.
HAVING filters data after it is grouped. Data does need to be grouped for HAVING.
HAVING can be used to filter aggregate functions. WHERE cannot.
-
Give me the UPDATE syntax.
- UPDATE tablename
- SET columnname = 'newvalue'
- WHERE {condition}
-
What are the DDL commands used for.
DDL statements are used to define the database structure or schema. e.g.
-
Whate are the DML commands for?
DML statements are used for managing data within schema objects. e.g.
- SELECT
- INSERT
- UPDATE
- DELETE
- MERGE
-
What is the difference between an inner and outer join?
An inner join is a join of two or more tables that returns only those rows that satisfy the join condition.
An outer join returns all rows that satisfy the join condition and also returns non matching rows based on Outer join type.
-
Explain what a Lost Update is and what strategies we can use to make sure it doesn't happen.
A Lost Update happens when one user updates data and then a second user updates the same data before the first transaction has comitted.
This can be prevented by
- Locking - Prevent the second user from viewing data that might have been modified.
- Timestamping - If the data has a lower timestamp than the current transaction prevent the second transaction from modifying it.
-
Give me the definition of a business rule
It's a brief, precise and unambiguous description of a policy, procedure or principle within a specific organisation.
They are used to define entities, attributes, relationships and constraints.
Knowing the business rules promotes the creation of an accurate data model based on how the organisation actually works and what role is played by the data within in an organisation.
Not all rules can be modeled but they can still be enforced by application software.
-
Give me some examples of business rules you might use.
- A customer can make many
- payments on account.
- Each payment on account is
- only credited to one customer.
- A customer can generate many
- invoices.
- Each invoice is generated by
- only one customer.
-
What are business rules actually for?
- They help standardise the companies view of the data.
- are a communications tool between users and designers.
They allow the designer to understand the nature, role and scope of the data.
They allow the designer to understand business processes.
They allow the deisgner to develop appropriate relationship participation rules and constraints.
-
What is a DBMS?
A DBMS is a set of software programs that controls the organisation, storage, management and retrieval of data in a database.
When a DBMS is used information systems can be changed more easily as the organisations information requirements change.
-
What is referential integrity?
Referential integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation.
For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key.
-
What are the three types of integrity constraints?
Referential Integrity
Entity Integrity
Domain Integrity
-
What are some typical database functions.
Define -
-
What is Entity Integrity?
Entity integrity is an integrity rule which states that every table must have a primary key and that the column or columns chosen to be the primary key should be unique and not null.
-
What is Domain Integrity?
This is for restricting data to predefined data types.
-
What is Referential Integrity?
The foreign key in the child must contain the data from the primary key of the parent.
-
Give me the main functionality of a DBMS
- Define - a particular database
- Construct - load the initial db contents on a secondary storage medium.
- Manipulation - Retrieval / Modification / Accessing
- Processing & Sharing
- Presentation
- Maintaining the data
A DBMS provides a DML and a DDL
-
What does Data Manipulation Mean
- Retrieval
- Insertion
- Deletion
- Modifications
A Data Dictionary / Catalogue is a file that contains metadata
-
Give me two advantages of good database design.
Reduce redundant information.
Reduces errors which can lead to poor decisions.
|
|
