PostgreSQL代写：COM5725 Hospital System Database and Data Interface Part2

发表于   |   分类于

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:

1
2
3
4
5
6
7
8
9
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:

1
2
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:

1
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

  1. name: The name of the sequence to be created.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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:

1
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.