Lambo Jayapalan's questions

Eu tenho uma tabela de usuários:

|Username|UserType|Points|
|John    |A       |250   |
|Mary    |A       |150   |
|Anna    |B       |600   |

e Níveis

|UserType|MinPoints|Level  |
|A       |100      |Bronze |
|A       |200      |Silver |
|A       |300      |Gold   |
|B       |500      |Bronze |

E estou procurando uma consulta para obter o nível de cada usuário. Algo na linha de:

SELECT *
FROM Users U
INNER JOIN (
    SELECT TOP 1 Level, U.UserName
    FROM Levels L
    WHERE L.MinPoints < U.Points
    ORDER BY MinPoints DESC
    ) UL ON U.Username = UL.Username

De modo que os resultados seriam:

|Username|UserType|Points|Level  |
|John    |A       |250   |Silver |
|Mary    |A       |150   |Bronze |
|Anna    |B       |600   |Bronze |

Alguém tem alguma idéia ou sugestão sobre como eu poderia fazer isso sem recorrer a cursores?

Se eu tiver uma tabela simples com 2 colunas e uma chave primária composta consistindo em ambas as colunas, como faço para renomear uma das colunas, mas apenas se não causar um conflito primário?

Aqui está um exemplo simplificado.

Pegue a seguinte tabela:

User  | Role
------|----------------
Joe   | User
Joe   | Administrator
Brian | User
Anna  | Administrator
Anna  | Admin

Suponha que eu queira escrever a seguinte consulta:

UPDATE UserRoles Set Role = 'Admin' WHERE Role = 'Administrator'

Sem usar cursores, como altero minha instrução SQL Update para evitar renomear Anna (porque esse usuário já tem a função Admin), mas ainda renomeio Joe?

Isso é possível usando SQL baseado em conjunto ou usando um cursor é a única maneira?

Obrigado.

Como faço para criar uma chave estrangeira composta para uma tabela com uma única chave primária? Por exemplo, suponha que eu tenha as seguintes tabelas:

CREATE TABLE Branches (
    BranchId UniqueIdentifier DEFAULT NewSequentialID(), 
    CONSTRAINT pk_BranchId PRIMARY KEY (BranchId)
);

CREATE TABLE Expectations (
    ExpectationId UniqueIdentifier DEFAULT NewSequentialID(), 
    BranchId UniqueIdentifier NOT NULL, 

    CONSTRAINT fk_Expectations_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId), 
    CONSTRAINT pk_ExpectationId PRIMARY KEY (ExpectationId)
);

CREATE TABLE ClassSections (
    ClassId UniqueIdentifier NOT NULL,
    BranchId UniqueIdentifier NOT NULL, 

    CONSTRAINT fk_ClassSections_ClassId FOREIGN KEY (ClassId) REFERENCES Classes(ClassId), 
    CONSTRAINT fk_ClassSections_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId), 
    CONSTRAINT pk_ClassSections PRIMARY KEY (ClassID, BranchId)
);

CREATE TABLE ClassExpectations (
    ClassId UniqueIdentifier NOT NULL, 
    BranchId UniqueIdentifier NOT NULL, 
    ExpectationId UniqueIdentifier NOT NULL, 

    CONSTRAINT fk_ClassExpectations_ClassId FOREIGN KEY (ClassId) REFERENCES Classes(ClassId), 
    CONSTRAINT fk_ClassExpectations_BranchId FOREIGN KEY (BranchId) REFERENCES Branches(BranchId), 
    CONSTRAINT fk_ClassExpectations_ExpectationId FOREIGN KEY (ExpectationId) REFERENCES Expectations(ExpectationId), 
    CONSTRAINT fk_ClassExpectations_ExpectationIdBranchId FOREIGN KEY (ExpectationId, BranchId) REFERENCES Expectations(ExpectationId, BranchId), 
    CONSTRAINT pk_ClassDueDates PRIMARY KEY (ClassId, ExpectationId)
);

Estou tentando adicionar a seguinte restrição, mas não permite:

ALTER TABLE ClassExpectations ADD CONSTRAINT fk_ClassExpectations_ExpectationIdBranchId FOREIGN KEY (ExpectationId, BranchId) REFERENCES Expectations(ExpectationId, BranchId);

Continuo recebendo o erro:

Não há chaves primárias ou candidatas na tabela referenciada 'Expectations' que correspondam à lista de colunas de referência na chave estrangeira

De que outra forma posso garantir que um ExpectationId nunca seja adicionado a ClassExpectations sem seu BranchId correspondente (da tabela Expectations)?

Estou querendo saber como arquitetar um banco de dados quando há vários caminhos (por meio de chaves estrangeiras) para uma tabela pai comum.

Por exemplo, tenho as seguintes tabelas:

Table: Students
PK StudentId

Table: Classes
PK: ClassId

Table: ClassStudents
PK: ClassStudentId:
FK: StudentId
FK: ClassId

Table: ClassAssignments
PK: ClassAssignmentId
FK: ClassId
FK: Assignment

Agora vou criar uma nova tabela que armazena os envios de Tarefas de cada aluno. Qual é a melhor maneira de estruturá-lo de forma que um aluno de outra turma não possa ser adicionado a uma tarefa desta turma?

TABLE: ClassStudentAssignments
FK: ClassAssignmentId
FK: ClassStudentId

Existe uma maneira de lidar com isso no design do banco de dados ou teria que ser feito apenas por meio da lógica de negócios?

Agradecemos antecipadamente por qualquer ajuda.