PostgreSQL的数据库第二次作业。前一次作业是画这个数据模型的E-R图，以及构建表的部分，这次需要代写插入数据的部分。
Data definition language
Data definition language (DDL) statements let you perform these tasks:
- Create, Alter, and Drop schema objects in your database;
- Grant and revoke privileges and roles;
- Analyze information on a table.
We will discuss the DDL statement components required by Project 2 in more
detail:
CREATE TABLE statement
A commonly used CREATE command is the CREATE TABLE command. The typical usage
is:
CREATE TABLE [table name] ([column definitions]) [table parameters]
An example statement to create a table named employees with a few columns is:
CREATE TABLE employees (
id
INTEGER
PRIMARY KEY,
first_name VARCHAR(50) not null,
last_name VARCHAR(75) not null,
fname VARCHAR(50) not null,
dateofbirth DATE not null
);
—|—
ALTER TABLE statement
The ALTER statement modifies an existing database object. An ALTER statement
in SQL changes the properties of an object. The typical usage is:
ALTER TABLE [table name] [table parameters]
For example, the command to add (then remove) a column named bubbles for an
existing table named sink is:
ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE sink DROP COLUMN bubbles;
—|—
DROP TABLE statement
The DROP statement destroys an existing database object. A DROP statement in
SQL removes a component from a relational database management system. The
typical usage is simply:
DROP TABLE [table name]
For example, the command to drop a table named employees is:
DROP TABLE employees;
—|—
CREATE SEQUENCE statement
The CREATE SEQUENCE statement creates a sequence, which is a database object
from which multiple users may generate unique integers. You can use sequences
to automatically generate primary key values. The typical usage is:
CREATE SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
If a schema name is given then the sequence is created in the specified
schema. Otherwise, it is created in the current schema. The sequence name must
be distinct from the name of any other sequence, table, index, or view in the
same schema.
Parameters
- name: The name of the sequence to be created.
- increment: The optional clause INCREMENT BY increment specifies which value is added to the current sequence value to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1.
- minvalue: NO MINVALUE. The optional clause MINVALUE minvalue determines the minimum value a sequence can generate. If this clause is not supplied or NO MINVALUE is specified, then defaults will be used.
- maxvalue: NO MAXVALUE. The optional clause MAXVALUE maxvalue determines the maximum value for the sequence. If this clause is not supplied or NO MAXVALUE is specified, then default values will be used.
- start: The optional clause START WITH start allows the sequence to begin anywhere. The default starting value is minvalue for ascending sequences and maxvalue for descending ones.
- cache: The optional clause CACHE cache specifies how many sequence numbers are to be pre-allocated and stored in memory for faster access. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the default.
- CYCLE or NO CYCLE: The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively. If NO CYCLE is specified, any calls to nextval after the sequence has reached its maximum value will return an error. If neither CYCLE nor NO CYCLE are specified, NO CYCLE is the default.
The following example creates the sequence Cust_Seq. This sequence could be
used to provide customer ID values when rows are added to the customers table.
DROP SEQUENCE statement
DROP SEQUENCE removes sequence number generators. A sequence can only be
dropped by its owner
or a superuser. The typical usage is simply:
DROP SEQUENCE [ IF EXISTS ] name […] [ CASCADE | RESTRICT ]
Data manipulation language
Data manipulation language (DML) comprises the SQL data change statements,
which modify stored data but not the schema or database objects. In
Deliverable 2, students are only required to turn in DML statements related to
the INSERT statement. Students are encouraged to practice the DELETE and
UPDATE statements by themselves. The syntax and usage of DELETE and UPDATE
statements are covered in the tutorial slides.
We will discuss the requirement in more detail:
INSERT statement
INSERT statement adds one or more records to any single table in a relational
database. Insert
statements have the following form:
INSERT INTO table (column1 [, column2, column3 … ]) VALUES (value1 [, value2, value3 … ])
Example:
INSERT INTO phone_book (name, number) VALUES (‘John Doe’, ‘555-1212’);
—|—
Data Access Interface
For each one of the entities in the miniworld, you must create all the
appropriate maintenance functions depending on the allowable actions over the
given entity. For instance, patients may be created, modified, and deleted;
therefore, you must create the following functions: create_patient (…),
update_patient(…), and delete_patient(…). Identifying the appropriate
arguments to each of these functions is part of your design job. Every entity
must have an ID. All IDs must be automatically drawn from a sequence, which
you must create explicitly. All create_entity(…) functions must return the ID
of the entity that was created.
We will cover the function implementation syntax in the tutorial slides. Here
we just give an example of a function of the type create_entity(…). For
instance, the following function create_card will insert a row of values in
the table Card:
Similarly, you also need to implement the function update_card to update one
particular attribute such as updated_card_number in the table Card given the
arguments declared in the function signature such as old_card_number and
customer_id. You will also implement the function delete_card to delete one
row in the table Card.
In Deliverable 2, each entity should have a set of appropriate maintenance
functions – create_entity (…), update_entity(…), and delete_entity(…).
Students should turn in an SQL file of maintenance functions for all entities
based on their relational data model design submitted in Project 1.
Important Note
As deliverables are related, you will be given the opportunity to improve your
previous work so that your future work does not carry over mistakes. In this
regard, when a deliverable is due, you will be given the opportunity to turn
in the previous deliverable if you revised it based on the feedback you
received when it was graded. The next due deliverable will then be graded
considering its consistency with the revised deliverable, not with the
original one. Note that this is optional and that it always applies to the
previous deliverable only, not to all previous deliverables. Additionally,
once a deliverableis graded, the score will not be changed even if you improve
it afterwards.
