10 - Fundamentals of Databases Flashcards

Question

What is the syntax for the INSERT command in SQL?

Answer 1

The INSERT command is used to add new records to an existing table: Full syntax: INSERT INTO (, , …) VALUES (, , …) Simplified (when using all columns in correct order): INSERT INTO

VALUES (, , …) Example: INSERT INTO Cars VALUES ("KA", "Ford", 3999, 2010, FALSE)

What are wildcards in SQL and how are they used?

Wildcards can be used in SQL commands to specify any possible value. In SQL, wildcards are usually notated with an asterisk (*). Example: SELECT * FROM Cars WHERE Price > 4000 This returns all attributes for cars with price greater than 4000. Special case: DELETE FROM Cars and DELETE * FROM Cars both delete all entries in the table.

How do you create a table using SQL?

SQL can be used to make new database tables with the CREATE command. This specifies the table name, attributes, data types, and keys: CREATE TABLE Artworks (Title VARCHAR(225), Artist VARCHAR(255), Date YEAR, PRIMARY KEY (Title, Artist)) This creates a table called Artworks with a composite primary key composed of Title and Artist attributes. New records can then be added using INSERT.

What is a client server database system?

A client server database system provides simultaneous access to a database for multiple clients. For example, social media websites store information on databases that are continuously being accessed and modified by different users simultaneously.

What is concurrent access and when does it become a problem?

Concurrent access occurs when different users attempt to access the same field at the same time. Issues rarely arise when two users are requesting access to different, unrelated fields in a database. However, concurrent access can result in database updates being lost if two users edit a record at the same time.

What are the four methods to manage concurrent access?

The four methods to manage concurrent access are: 1. Record locks 2. Serialisation 3. Timestamp ordering 4. Commitment ordering Each method provides a different approach to controlling how multiple users can access the same database records simultaneously.

How do record locks work?

When a record is accessed by one user, it is immediately locked to other users until the first user has finished using it. Other users are blocked from accessing or modifying the content of a field until it has been unlocked.

How does serialisation work in database management?

Rather than locking a field, requests from other users are placed in a queue. Once the first user has finished using the field, the next command in the queue is executed and so on. This ensures orderly processing of database requests.

How does timestamp ordering work?

When multiple commands are sent to the same field in a database, each is assigned a timestamp which marks the point in time at which the command was initiated. Commands are carried out on the field in the order of their timestamps.

How does commitment ordering work?

When a database uses commitment ordering, an algorithm is used to work out an optimum order in which to execute commands for the same field. This algorithm will take into account the impact of commands on other parts of the database and attempt to minimise issues from occurring with the database.

Answer 2