печаль с sql

[vicvic90]

CREATE TABLE "Место рождения" (
"кодГорода" INTEGER NOT NULL IDENTITY,
"Название" VARCHAR(25) NOT NULL,
CONSTRAINT "K5" PRIMARY KEY ("кодГорода")
)

CREATE TABLE "Гражданин" (
"№Гражданина" INTEGER NOT NULL IDENTITY,
"Фамилия" VARCHAR(25) NOT NULL,
"Имя" VARCHAR(25) NOT NULL,
"Отчество" VARCHAR(25) NOT NULL,
"Дата рождения" DATE NOT NULL,
"Воинская обязанность" VARCHAR(3) NOT NULL,
"Пол" VARCHAR(1) NOT NULL,
"кодГорода" INTEGER NOT NULL,
CONSTRAINT "K1" PRIMARY KEY ("№Гражданина"),
CONSTRAINT "FK1" FOREIGN KEY ("кодГорода") REFERENCES "Место рождения" ("кодГорода"),
CONSTRAINT "c1" CHECK ("Пол"='ж' or "Пол"='м'),
CONSTRAINT "c2" CHECK ("Воинская обязанность" = 'да' or "Воинская обязанность" ='нет')

)

CREATE TABLE "Паспорт" (
"№Паспорта" INTEGER NOT NULL IDENTITY,
"№Гражданина" INTEGER NOT NULL,
"Дата выдачи" DATE NOT NULL,
CONSTRAINT "K2" PRIMARY KEY ("№Паспорта"),
CONSTRAINT "FK2" FOREIGN KEY ("№Гражданина") REFERENCES "Гражданин" ("№Гражданина")
)

CREATE TABLE "Семейное положение"(
"№Гражданина(муж)" INTEGER NOT NULL,
"№Гражданина(жена)" INTEGER NOT NULL,
"Дата регистрации" DATE NOT NULL,
CONSTRAINT "K3" PRIMARY KEY ("№Гражданина(муж)", "№Гражданина(жена)"),
CONSTRAINT "FK3" FOREIGN KEY ("№Гражданина(муж)") REFERENCES "Гражданин" ("№Гражданина"),
CONSTRAINT "FK4" FOREIGN KEY ("№Гражданина(жена)") REFERENCES "Гражданин" ("№Гражданина")
)

CREATE TABLE "Дети" (
"№Гражданина(реб.)" INTEGER NOT NULL,
"№Гражданина" INTEGER NOT NULL,
CONSTRAINT "K4" PRIMARY KEY ("№Гражданина(реб.)","№Гражданин а" ),
CONSTRAINT "FK8" FOREIGN KEY ("№Гражданина") REFERENCES "Гражданин" ("№Гражданина"),
CONSTRAINT "FK5" FOREIGN KEY ("№Гражданина(реб.)") REFERENCES "Гражданин" ("№Гражданина")
)

CREATE TABLE "Прописка" (
"Улица" VARCHAR(25) NOT NULL,
"Дом" INTEGER NOT NULL,
"Квартира" INTEGER NOT NULL,
CONSTRAINT "K6" PRIMARY KEY ("Улица","Дом","Квартира")
)

CREATE TABLE "прописка гражданина" (
"№Гражданина" INTEGER NOT NULL,
"Улица" VARCHAR(20) NOT NULL,
"Дом" INTEGER NOT NULL,
"Квартира" INTEGER NOT NULL,
"Дата" DATE NOT NULL,
CONSTRAINT "K7" PRIMARY KEY ("№Гражданина","Улица","Дом","Кварт ира"),
CONSTRAINT "FK6" FOREIGN KEY ("Улица","Дом","Квартира") REFERENCES "Прописка"("Улица","Дом","Квартира" ),
CONSTRAINT "FK7" FOREIGN KEY ("№Гражданина") REFERENCES "Гражданин" ("№Гражданина")
)


такая база по ней задания.

Вопросы по схеме базы и добавлению в нее объектной специфики:

1. В схеме не хватает ограничений целостности.
Если хранится только актуальная информация, то у каждого гражданина должно быть не больше одного паспорта и, возможно, не больше одной прописки (не считая временной?)
Если хранится также историческая информация, то нужны сведения о том, когда та или иная информация была актуальна.
Следует также добавить ограничения целостности для семейного положения и детей.

2. В схеме должен быть общий элемент.
Как адрес прописки, так и место рождения должны содержать название субъекта федерации и населенный пункт.
Следует добавить эту информацию и сделать ее структурным типом.
Аналогично, структурным типом сделать то, что хранится в таблице "Прописка".
Еще один потенциальный структурный тип - ФИО.

3. Добавить методы для типов.
Например, для типа ФИО - выдать полное ФИО, выдать краткое ФИО.
Аналогично - разные варианты выдачи адреса и т.д.

4. Предполагая, что для гражданина может храниться несколько адресов прописки (в разное время или временных), реализовать их вложенной таблицей либо массивом для каждого гражданина.

Задания на составление SQL-запросов:

1. Найти, какой процент указанных в базе граждан родился в том же населенном пункте, где сейчас проживает. Тех, для кого один из этих адресов не указан - игнорировать.
2. Для каждого гражданина выдать общее количество его потомков в базе (его детей, детей их детей и т.д.), в предположении, что информация о всех детях всех родителей хранится в базе - сделать иерархический запрос.
3. Выдать ФИО, место рождения и последний по времени адрес прописки для всех военнообязанных граждан, которые не женаты и не имеют детей.



Кто может помочь за вознаграждение?
почта: victoria4ever@mail.ru